Home » SQL & PL/SQL » SQL & PL/SQL » Help in Join
Help in Join [message #203747] Thu, 16 November 2006 03:36 Go to next message
Ashokragauv
Messages: 13
Registered: June 2006
Location: Pune
Junior Member

Hi Gurus,

I have 3 Tables(Table1,Table2,Table3),
Table1 has 1000 Records,
Table2 has 300 Records and
Table3 has 500 Recores

All the three tables are having customer Key as primary Key, Table 1 has duplicate in customer key...
Table2 and Table3 are having unique customer keys,

My requirement is

I want to select customer_key and customer_type_code from Table1 who are all matching with Table2 and Table3

currently i am using this ...

select customer_key,
customer_type_code
from
Table1 T1,
Table2 T2
where T1.customer_key = T2.customer_key

UNION ALL

select customer_key,
customer_type_code
from
Table1 T1,
Table2 T3
where T1.customer_key = T2.customer_key

How to get the same result without using Unionall

Thanks in Advance
Re: Help in Join [message #203761 is a reply to message #203747] Thu, 16 November 2006 04:27 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Something like this?
SELECT a.customer_key, a.customer_type_code
FROM table1 a, table2 b, table3 c
WHERE a.customer_key = b.customer_key
  AND b.customer_key = c.customer_key;
Re: Help in Join [message #203771 is a reply to message #203747] Thu, 16 November 2006 04:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ashokragauv wrote on Thu, 16 November 2006 10:36
All the three tables are having customer Key as primary Key, Table 1 has duplicate in customer key


That does not make sense. If it is a primary key, there are no duplicates.
Re: Help in Join [message #203777 is a reply to message #203761] Thu, 16 November 2006 05:08 Go to previous messageGo to next message
Ashokragauv
Messages: 13
Registered: June 2006
Location: Pune
Junior Member

Sorry for the mistake, Table1 will be having multiple Customer Key Cust_key is not primary key for Table1

Re: Help in Join [message #203779 is a reply to message #203761] Thu, 16 November 2006 05:14 Go to previous messageGo to next message
Ashokragauv
Messages: 13
Registered: June 2006
Location: Pune
Junior Member

Littlefoot wrote on Thu, 16 November 2006 15:57
Something like this?
SELECT a.customer_key, a.customer_type_code
FROM table1 a, table2 b, table3 c
WHERE a.customer_key = b.customer_key
  AND b.customer_key = c.customer_key;



Hi Littlefoot

Thanks for your reply,

Table1 has 1000 Rows
Table2 has 300 Rows (subset of Table1)
Table3 has 500 Rows (subset of Table1)

if you join B.customer_key and C.customer_key that will match only 200 rows, as per your query i will get only 200 Rows as output, my expected output is 800 Rows

Thanks in advance


Re: Help in Join [message #203784 is a reply to message #203779] Thu, 16 November 2006 05:27 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So use outer join instead.
Previous Topic: Regular Expression
Next Topic: PL/SQL resrved words
Goto Forum:
  


Current Time: Sat Dec 10 09:04:31 CST 2016

Total time taken to generate the page: 0.12724 seconds