Home » SQL & PL/SQL » SQL & PL/SQL » using in and = conditions
using in and = conditions [message #192947] Thu, 14 September 2006 06:15 Go to next message
Messages: 63
Registered: March 2005

I have table1 and table2

table1 columns


table2 columns


and other fields in both the tables.

table1 data
id fname lname
1 jon abc
2 jon bcd
3 jon efg

table2 data

fname lname address val
jon xyz aaaa 10
jon xyz aaaa 20
jon pqr aaaa 30

Now when I match both the tables using equi joins on fname field then I am getting 9 records.

select a.id,b.fname,b.address,b.val from table1 a,table2 b
where a.fname=b.fname;

when I try the other option as below

select fname,address,val from table2 where fname in
(select fname from table1)

I am getting 3 records only.

I want the difference of both the queries and also
I need my output
with id from first table and address,val from 2nd table.
which query should I use.

output should be

id address val
1 aaaa 10
2 aaaa 20
3 aaaa 30

thanks in advance

Re: using in and = conditions [message #192960 is a reply to message #192947] Thu, 14 September 2006 06:57 Go to previous message
Messages: 74
Registered: September 2006
infact in joining, the fname value of the first row is matched with the all rows of the table and they all matched, and then that is the same for othertwo fnames in table 1 so thats why you are getting 9 records. i.e. (3x3=9).

But in case of the IN and subquery you are only selecting data from table2, the data of the table 1 is just being used by the query to search in the table2. As there are only three rows matched in the table 2 so thats why you get three rows only.

To get the desired output you will have to change your desin a bit. Put some column in both tables who has a proper unique identification for each row in both tables.

Previous Topic: question about "set termout off" and "java stored procedure "
Next Topic: how to insert txt file content into my oracle table
Goto Forum:

Current Time: Sun Aug 20 06:49:55 CDT 2017

Total time taken to generate the page: 0.11085 seconds