Home » SQL & PL/SQL » SQL & PL/SQL » Simple Query that involve prefix (Oracle, Linux)
Simple Query that involve prefix [message #357661] Thu, 06 November 2008 03:23 Go to next message
albogp
Messages: 2
Registered: November 2008
Junior Member
Hi all of Oracle master

First of all, please forgive me if my English is not good. I am from Indonesia.
I am a new guy in the PL SQL world, and I need your help.

Let's say I have two tables.

Table A
---------
number
---------
11111
11122
11133
22222
77777
55555
88888


and


Table B
---------
prefix
---------
111
222
333


How can I select all of number in table A that have different prefix with table B's prefix?

From the example :
111 is the prefix for 11111, 11122, and 11133
222 is the prefix for 22222
So I don't want to select number 11111, 11122, 11133, and 22222. I just want to select 77777, 55555, and 88888.


Thanks.
Best Regards,
Albo
Re: Simple Query that involve prefix [message #357665 is a reply to message #357661] Thu, 06 November 2008 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and post what you already tried.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

In the end, it is a good practice in order to get a quick answer to post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Simple Query that involve prefix [message #357667 is a reply to message #357661] Thu, 06 November 2008 03:41 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Clues :

NOT EXISTS and LIKE

Smile
Rajuvan.
Re: Simple Query that involve prefix [message #357668 is a reply to message #357661] Thu, 06 November 2008 03:44 Go to previous messageGo to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Pak albogp,

May not be the best solution. But you can achieve it by using following sql query:

select COL 
from   TABLE1
where  COL not in (select t1.COL 
                   from   TABLE1 t1, 
                          TABLE2 t2 
                   where  t1.COL like t2.PREFIX || '%');


Thanks,
Prashanth.
Re: Simple Query that involve prefix [message #357670 is a reply to message #357668] Thu, 06 November 2008 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel
Re: Simple Query that involve prefix [message #357677 is a reply to message #357661] Thu, 06 November 2008 04:10 Go to previous messageGo to next message
deepshikhahcl
Messages: 14
Registered: October 2008
Junior Member
You can try the following
select * from A where substr(col,1,3) NOT IN(select prefix from B);
Re: Simple Query that involve prefix [message #357685 is a reply to message #357661] Thu, 06 November 2008 04:35 Go to previous messageGo to next message
albogp
Messages: 2
Registered: November 2008
Junior Member
Hi All,

Thanks very much for the replies, especially for Mr. Prashanth.
Actually I'm not a student, but a core module engineer. I'm not familiar with SQL statement, but suddenly my manager ask me to take over several Store Procedure, because the previous owner was leaving the company. This is an urgent situation for me.

Best Regards
Re: Simple Query that involve prefix [message #357688 is a reply to message #357661] Thu, 06 November 2008 04:47 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Actually doesn't matter who you are . It is as per the Forum Guide recommend the users to give clues or hints especially when question seems to be newbie's question Smile

Smile
Rajuvan.
Previous Topic: DYNAMIC Record?
Next Topic: Enforcing business rule
Goto Forum:
  


Current Time: Fri Dec 09 00:29:40 CST 2016

Total time taken to generate the page: 0.20867 seconds