Which Row to Return on the Right of a Left Join? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Which Row to Return on the Right of a Left Join?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-02 : 07:46:04

Greg writes "For example:

Table A has 1 row.
Table B has 5 rows.

If you do TableA LEFT JOIN TableB ON TableA.id=TableB.id, you will only end up with one row returned. That row will be Table A's row joined with the first row in Table B. How could I get it to return a different row from Table B based on criteria? Thanks for your help!"

mr_mist
Grunnio

1870 Posts

Posted - 2003-01-02 : 08:23:53

What criteria?

Your assumption that it will return only one row is incorrect :

set nocount on
create table #moo ( thing int null)
create table #moo2 ( thing int null, doobery int null)

insert into #moo values (1)

insert into #moo2 values (1,1)
insert into #moo2 values (2,1)
insert into #moo2 values (3,1)
insert into #moo2 values (4,1)
insert into #moo2 values (1,2)

set nocount off
SELECT * from #moo
left join #moo2 on
#moo.thing = #moo2.thing

drop table #moo
drop table #moo2

It will return one row for each match.
Maybe try a right outer join?

-------
Moo.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-02 : 10:14:47

Not sure, but you may be asking how can you criteria on the right table of a LEFT OUTER join.

In other words,

SELECT Table1.*, Table2.*
FROM Table1
LEFT OUTER JOIN
Table2
ON Table1.ID = Table2.ID
WHERE
(some criteria for table2)

In the above, adding criteria on Table2 negates the LEFT OUTER JOIN and turns it into an INNER JOIN.

There are two easy solutions:

SELECT Table1.*, Table2.*
FROM Table1
LEFT OUTER JOIN
(SELECT * FROM Table2 WHERE ....)
ON Table1.ID = Table2.ID

- OR -

SELECT Table1.*, Table2.*
FROM Table1
LEFT OUTER JOIN
Table2
ON Table1.ID = Table2.ID
AND
(some criteria for table2)

The first moves the criteria for table2 into a subquery which is evaluated before the JOIN. The second solution puts the criteria in as part of the JOIN expression, therefore keeping the OUTER JOIN intact.

Not sure if that was the question, but just in case .....

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources