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 |
|
Disco
Starting Member
4 Posts |
 Posted - 2014-11-09 : 07:44:21
|
| Hi guys,My name is Paul. I am new to the forum and pretty much to the SQl. I have a question if you could help me here a bit it would be great! Thank you very much in advance!Basically we have three simple tables: clients, products, orders clients table looks like this: cid client------------------ 1 John 2 Peterproducts table looks like this: pid prod------------------ 1 Sony 2 TDKorders table looks like this: cid pid price----------------------- 1 1 777 1 2 888We need to write a single SQL query to get this result : client prod price -------------------------------------- John Sony 777 John TDK 888 Peter Sony NULL Peter TDK NULLThat's it. Looks very simple and I hope it is easy for you experts to do. Thank you again! |
|
|
mandm
Posting Yak Master
120 Posts |
Posted - 2014-11-09 : 08:30:36
|
| How are you arriving at that output? It looks like John bought both products and Peter didn't buy anything.I see you want to see all customers and products and possible orders. It's too early in the morning for me.Try this SELECT c.Client , p.Prod , o.Price FROM Clients AS cCROSS APPLY Products AS p LEFT JOIN Orders AS o ON c.cid = o.cid AND p.pid = o.pid ORDER BY c.Client |
 |
|
|
Disco
Starting Member
4 Posts |
Posted - 2014-11-09 : 09:11:55
|
| Thank you for your response.Yes in the output, all and every client(in the first table: John and Peter) needs to be listed with every product (in the second table: Sony and TDK), no matter if they purchased it or not. And the only "price" column (in the output) will have NULL for those items that have not been purchased by a customer. |
|
|
|
mandm
Posting Yak Master
120 Posts |
Posted - 2014-11-09 : 09:16:45
|
| I just edited my response with a query that should work. |
|
|
|
Disco
Starting Member
4 Posts |
Posted - 2014-11-09 : 09:23:25
|
| Excellent! thank you very much.Allow me just a minute or 10 to figure out what I just got haha yes I am slow ! Plus to be honest I have never heard of CROSS APPLY operator yet. I need to take a pause to learn about it a bit. I'll be back! hahaha and Thank you! but don't run away yet please as I will most definitely need help again ;-] |
|
|
|
mandm
Posting Yak Master
120 Posts |
Posted - 2014-11-09 : 09:42:11
|
| CROSS JOIN and CROSS APPLY are simply a way to join two tables that have no relationship to each other as is the case between Clients and Products. The main difference between the APPLY and the JOIN is that APPLY can also be used with a table valued function. |
|
|
|
Disco
Starting Member
4 Posts |
Posted - 2014-11-09 : 10:09:24
|
| Wow so cool! Thank you. I need so much more to learn and practice =( |
|
|
|
mandm
Posting Yak Master
120 Posts |
Posted - 2014-11-09 : 14:27:29
|
| It just takes time and a desire to learn. |
|
|
|
|
|
|
New to the Forum with Question