| Author |
 Topic  |
|
|
ebi007
Starting Member
15 Posts |
 Posted - 05/25/2012 : 06:14:04
|
hi everybody
i'm new to sql server programming , and i'd like to have any help about this report from northwind sample database :
Print the product ID and name of all
products that have NOT been used in orders.
there is no limits only God can stop me. |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 05/25/2012 : 06:33:03
|
SELECT
p.ProductID,
p.ProductName
FROM Products as p
LEFT JOIN [Order Details] as od
ON od.ProductID = p.ProductID
WHERE od.ProductID IS NULL
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
ebi007
Starting Member
15 Posts |
Posted - 05/25/2012 : 06:53:14
|
your query shows nothing on the output.
the output is:
ProductId,ProductName.
NULL NULL
------------------------------------------------------------------------
there is no limits only God can stop me.
|
Edited by - ebi007 on 05/25/2012 06:57:49 |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 05/25/2012 : 07:12:57
|
That is right because there are no products that not have been used in orders.
To have a test you can insert a new product without using it in an order.
insert Products(ProductName) select 'test'
After that you can try my SELECT again and you will see. 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
ebi007
Starting Member
15 Posts |
Posted - 05/25/2012 : 07:32:36
|
thanks webfred for your answer but there is a problem that query must return 6 rows.
---------------------------
there is no limits only God can stop me. |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 05/25/2012 : 07:45:27
|
I don't know which exercises you are going through but maybe you have missed some inserts before?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
ebi007
Starting Member
15 Posts |
Posted - 05/25/2012 : 08:04:20
|
quote:
Originally posted by webfred
I don't know which exercises you are going through but maybe you have missed some inserts before?
No, you're never too old to Yak'n'Roll if you're too young to die.
the northwind database has many orders in [orders details] table.
yes there are some products which have not been ordered.
just check the [orders details] table.
---------------------------
there is no limits only God can stop me. |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 05/25/2012 : 08:19:00
|
did you get 71 rows in the question before or maybe 77?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
ebi007
Starting Member
15 Posts |
Posted - 05/25/2012 : 08:58:37
|
for the previous question i got 77 rows but it's false the right answer is 71 rows.
where is the problem now ?
---------------------------
there is no limits only God can stop me. |
|
|
|
ebi007
Starting Member
15 Posts |
Posted - 05/25/2012 : 09:15:03
|
i think there is an error somewhere,everytime a product is ordered,there is new record in the orders table and details about that order in [order details] table.
---------------------------
there is no limits only God can stop me. |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 05/25/2012 : 09:59:51
|
quote:
Originally posted by ebi007
for the previous question i got 77 rows but it's false the right answer is 71 rows.
where is the problem now ?
---------------------------
there is no limits only God can stop me.
This is the answer to your problem.
You want to get 6 entries (product not in any orders)
You are getting nothing because the missing 6 products already used in orders.
77 instead of 71 rows in the previous question is attesting that.
And btw.: I know that my formerly given solution IS RIGHT but the data isn't
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
ebi007
Starting Member
15 Posts |
Posted - 05/25/2012 : 10:17:43
|
thank you fred, you're totaly right.
---------------------------
there is no limits only God can stop me. |
|
|
| |
Topic |
|
need help on a query.
