t-sql 2008 left join - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

t-sql 2008 left join

Author

Topic

scamper
Yak Posting Veteran

52 Posts

Posted - 2014-05-20 : 11:06:14

In t-sql 2208 r2, I have sql that looks like the following:

select a.custname, i.item, i.amount, so.specialitem, so.specialamount
from customer a
inner join inventory i on a.custid = i.custid
left outer join on specialorder so on so.custid = so.custid

The problem is with the left outer join on the specialorder table.
Only about 50% of the time is there a special order. In rare cases,
there are 2 or more specialorder items that a customer picks.

My problem is that the user only wants one spcialorder item to be reported on.
The user does not care which specialorder item is appearing, they just one one
item selected.

Thus can you tell me how to change the sql listed above so that I can do
what my user wants to see?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-20 : 11:19:00

I assume your sql has a typo on the correlation (both custids are from table [so.]) that will give you a lot of rows

here's one way:


select a.custname, i.item, i.amount, so.specialitem, so.specialamount
from customer a
inner join inventory i on a.custid = i.custid
outer apply (
       select top 1 so.specialitem
                     ,so.specialamount
       from   specialorder so
       where  so.custid = a.custid
       order by specialamount desc --order by anything you want here
       ) so

Be One with the Optimizer
TG

scamper
Yak Posting Veteran

52 Posts

Posted - 2014-05-20 : 12:51:44

What is the purpose of the 'outer apply'? Are there other sql options that will solve this problem?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-20 : 13:38:20

quote:
Originally posted by scamper

What is the purpose of the 'outer apply'? Are there other sql options that will solve this problem?

Apply was introduced in sql version 2005. outer apply vs. cross apply: cross apply is analogous to inner join where as outer apply is analogous to outer join.

Yes, there are almost always several different ways to accomplish any sql task. Another way is to left outer join to a derived table which returns just one specialOrder row per custid.

Be One with the Optimizer
TG

scamper
Yak Posting Veteran

52 Posts

Posted - 2014-05-21 : 14:15:56

Thanks very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources