Bringing back results when tables have none.

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

Transact-SQL (2005)

Bringing back results when tables have none.

Author

Topic

robc
Yak Posting Veteran

60 Posts

Posted - 2008-10-08 : 20:36:25

Hi i'm bringing back a list of users and now i'd like to add some other data to my query.

I want to join onto an orders table and grab the membership Type they belong to. The thing is i'd still like to bring back results for that user even if they don't have a record in my orders table.

I'm joining 3 tables "usertable","orders" and "memberShips".

How could i join them so that userdata comes back even if they don't have a membership. Also, from my orders table i must retrieve their most recent order. I hope this is enough info.

Thanks
Rob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 01:02:36

this will give u what u want

select *
from usertable u
left join orders o
on o.linkingcol=o u.linkingcol
left join memberships m
on m.linkingcol=u.linkingcol

i dont know columns by which ytables have linked so i've put linking col. replace it by actual col names. also replace * by columns you want

EDIT:fixed typo in alias

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-10-09 : 11:13:35

Is that a typo on the join for the orders table? Shouldn't it be o.linkingcol = u.linkingcol?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 12:05:36

quote:
Originally posted by andros30

Is that a typo on the join for the orders table? Shouldn't it be o.linkingcol = u.linkingcol?

yup it was a typo. thanks for spotting out. i've edited the solution posted accordingly. cheers

robc
Yak Posting Veteran

60 Posts

Posted - 2008-10-15 : 17:21:42

Hi,

Thanks.

That works, except this brings back a record for each order in the orders table. I just want one record for each user, that includes info from their last order.

rc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 03:05:09

[code]SELECT *
FROM
(
select row_number() over (partition by u.userid order by orderid desc) as seq,*
from usertable u
left join orders o
on o.linkingcol=u.linkingcol
left join memberships m
on m.linkingcol=u.linkingcol

)t
where seq=1[/code]
assuming userid & orderid are pks of usertable & orders

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-10-16 : 09:00:23

or you can use something like

left outer join orders o on o.orderid=(select max(orderid) from orders o2 where o2.userid=u.userid)

for more 'classical' SQL

I'm not sure if it makes any difference. You'll have to use the analyser to see.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources