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.
| Author |
Topic |
|
Paul Skinner
Starting Member
9 Posts |
 Posted - 2007-12-10 : 06:31:35
|
I have this database running (ignore that the ERD below was done in Access, this is being made in Microsoft SQL Server 2005). What I need to do is if you look at the users table and the orderContents table I need to make a query that:Collects the users' names and any products they have bought (preferably shown by title not ID) as well as still showing the users that have not ordered any products.I get the feeling there's a left join involved, but can't quite see how to do it.Thank you in advanced for any help. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-10 : 06:37:49
|
SELECT u.*, o.*, oc.*FROM Users AS u LEFT JOIN Orders AS o ON o.CustomerID = u.CustomerIDLEFT JOIN OrderContents AS oc ON oc.OrderID = o.OrderID
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-12-10 : 06:38:11
|
| [code]Select c.CustomerNameFirst, c.CustomerNameLast, Isnull(p.ProductTitle, 'N/A') as ProductTitle, IsNull(t.quantity, 0) as Quantityfrom users c LEFT JOIN (Select * from orders o join OrderContents oc on o.OrderID = oc.OrderID) t on c.CustomerID = t.CustomerID[/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
Paul Skinner
Starting Member
9 Posts |
Posted - 2007-12-10 : 06:43:20
|
| Thank you for your quick responses.Peso: That works nicely, but misses out the product title. However, I am sure I can fix that. Thank you.*Edit* In fact it's done already. I have the hang of LEFT JOIN again now. Thank you.Harsh_Athalye: That produces an error."Msg 8156, Level 16, State 1, Line 1The column 'orderID' was specified multiple times for 't'.Msg 4104, Level 16, State 1, Line 1The multi-part identifier "p.ProductTitle" could not be bound."However, thank you for your speedy help. |
|
|
|
|
|
|
|
|
Left Join Help (Probably) - Query Merging anyway