Home » SQL & PL/SQL » SQL & PL/SQL » Table Joins
Table Joins [message #288576] Tue, 18 December 2007 01:31 Go to next message
sbadriprasad
Messages: 14
Registered: September 2007
Junior Member
hi,

i have two tables, table1 and table 2. table 1 has a primary key and table2 has a foreign key.
it is not compulsory that for each table1 record there should be a record in table 2, and
also for each table1 record there can be many records in the table 2. (simply saying it is one to many records)

for this type of tables i need to write a join to make sure i will not get repeated columns of table1. and also when there in no records mathing in table 2 i should get Null and other values of table1.

for the second condition i have achived by LEFT OUTER JOIN but the problem is when i have two records in table 2 i am getting two records in the results. i dont want this.
please help me which join i should go for this condition.
Re: Table Joins [message #288578 is a reply to message #288576] Tue, 18 December 2007 01:36 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

the problem is when i have two records in table 2 i am getting two records in the results. i dont want this.

So, you want only one record? Which one of them?
Re: Table Joins [message #288579 is a reply to message #288576] Tue, 18 December 2007 01:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Tell us what you DO want when there are multiple records in the detail-table.
You will get multiple records in your resultset, each containing the columns you select from the master plus the columns you select for each of the detailrecords.
Re: Table Joins [message #288628 is a reply to message #288579] Tue, 18 December 2007 03:48 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT a.*, b.fk
FROM   master a
LEFT OUTER JOIN (
    SELECT DISTINCT fk
    FROM   detail
) b ON b.fk = a.pk


Ross Leishman
Re: Table Joins [message #288631 is a reply to message #288628] Tue, 18 December 2007 04:01 Go to previous messageGo to next message
sbadriprasad
Messages: 14
Registered: September 2007
Junior Member
hi,
thank you for your reply. first i thaught this query will solve my problem but when i implement this solution i found that from the second table other than the column which we used for distinct(ie fk key) we cant use other columns in the select statement. but i need other columns also in the query?
for this what we can do?
Re: Table Joins [message #288636 is a reply to message #288576] Tue, 18 December 2007 04:07 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Ok, Just put the other column in the select clause.

Kiran.
Re: Table Joins [message #288639 is a reply to message #288636] Tue, 18 December 2007 04:11 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
All of the other columns may have different values on the multiple rows - you must pick one.
If you want any value, you can use MIN or MAX:

SELECT a.*, b.*
FROM   master a
LEFT OUTER JOIN (
    SELECT fk, MIN(col1), MIN(col2)
    FROM   detail
    GROUP BY fk
) b ON b.fk = a.pk


Ross Leishman
Previous Topic: Constraints
Next Topic: PFILE and SPFILE
Goto Forum:
  


Current Time: Thu Dec 08 04:07:32 CST 2016

Total time taken to generate the page: 0.13186 seconds