1

Tables joinedi have a homework question im having some trouble with. I am asked to a total number of products form our database that have never been sold. TABLES

it should look like this:

+--------------------------------------------+
| number of products that have not been sold |
+--------------------------------------------+
|                                        228 |
+--------------------------------------------+

I keep getting this:

+--------------------------------------------+
| number of products that have not been sold |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+

my query is: 

SELECT count(*) AS 'number of products that have not been sold'  
FROM orderdetail  
JOIN invoice on invoice.invoiceid=orderdetail.invoiceid  
WHERE productid is null;
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
cybkty
  • 9
  • 1
  • 1
  • 4

2 Answers2

3
  • Since you want to consider all the products, product table must be your starting table in the Join.
  • Do Left join instead of Inner join. Otherwise, Inner Join will eliminate all the product(s) where there is no matching Order row available.
  • Join to the orderDetail table using ProductID
  • To count all the product(s) having no order, use COUNT() function

Try the following:

SELECT count(p.ProductID) AS 'number of products that have not been sold'  
FROM product AS p   
LEFT JOIN orderDetail AS od ON od.ProductID = p.ProductID  
WHERE od.productid IS NULL
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • I seem to still get a total of 0. I also have to mention that product id goes from 1,2,5,6,7,8,10,12 etc so some productid's are missing from the column. – cybkty Oct 04 '18 at 20:40
  • @cybkty To just get the product id, you can remove `COUNT(DISTINCT p.productID)` and simply use `p.productID` – Madhur Bhaiya Oct 04 '18 at 20:41
  • @cybkty are you sure that there are some productid(s) which have no matching row in `orderdetail` table ? – Madhur Bhaiya Oct 04 '18 at 20:42
  • 1
    Assuming `ProductID` is the primary key of `product`, there's no need for `DISTINCT` since there can't be duplicates. – Barmar Oct 04 '18 at 20:43
  • And there's no need for `COUNT(p.ProductID)`, just use `COUNT(*)`. – Barmar Oct 04 '18 at 20:43
  • @Barmar yes true. I like to avoid (*) and specify the column in COUNT. It avoids unintended behaviours (sometimes) – Madhur Bhaiya Oct 04 '18 at 20:43
  • You would need `COUNT(DISTINCT p.ProductID)` if you were counting the products that have been ordered, since a product can be in multiple orders. – Barmar Oct 04 '18 at 20:45
  • @Barmar I have updated and removed the `DISTINCT` clause. – Madhur Bhaiya Oct 04 '18 at 20:45
  • I have added a picture of the invoice and orderdetail table as well – cybkty Oct 04 '18 at 20:51
  • @cybkty please use `left join` not `join`. There is a [difference between Inner and Left Join](https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join) – Madhur Bhaiya Oct 04 '18 at 20:54
  • @MadhurBhaiya it still seems to not work, keep getting 0 – cybkty Oct 04 '18 at 21:16
  • @cybkty there was a typo :-/ please check the updated answer now. It should definitely work! – Madhur Bhaiya Oct 05 '18 at 03:34
0

You can try this: leftjoin on product instead of orderdetail:

SELECT count(p.productid)
FROM product as p
LEFT JOIN orderdetail as oDetail on p.productid = oDetail.productid
WHERE oDetail.productid IS NULL;
Gauravsa
  • 6,330
  • 2
  • 21
  • 30