I need some help solving a SQL query, I need to list the names of consumers requesting at least one product but not requesting milk

Question

I need help solving the query "List names of customers that are requesting at least one product but not requesting milk. I have a rough start on it, but I have no idea how to finish it. Here's what I have so far.

SELECT DISTINCT 
    Tb_Consumer.Con_ID, Tb_Consumer.Name
FROM 
    Tb_Consumer, Tb_Requests
WHERE 
    Tb_Consumer.Con_ID = Tb_Requests.Con_ID  
    AND Tb_Consumer.Con_ID NOT IN (SELECT Tb_Consumer.Con_ID)

The output I'm looking for is this.

Con_id    Name
----------------
  2       Hammer
  4       Hoffer

The problem statement is ambiguous. Does "but no requesting milk" mean one product other than milk, or that any request for milk disqualifies the consumer from the query completely? — Joel Coehoorn, Apr 02 '19 at 19:06
Also: `TableA, TableB` joins, as with `Tb_Consumer, Tb_Requests`, have been obsolete for more than 25 years now. **Don't use them!** Write out `INNER JOIN` or `LEFT JOIN` and put the join conditions in the `ON` clause. — Joel Coehoorn, Apr 02 '19 at 19:08
And what does this even do `Not in (SELECT Tb_Consumer.Con_ID)`? — Eric, Apr 02 '19 at 19:10
Without sample input, how are we supposed to magically generate the output you want? — Eric, Apr 02 '19 at 19:11
I just noticed, why have you tagged `sqliteopenhelper`? That has nothing to do with SQL Server. — Thom A, Apr 02 '19 at 19:24

Zaynul Abadin Tuhin · Answer 1 · 2019-04-02T19:20:31.653

1

use not exists i assume you have a product_name column in table Tb_Requests

    select distinct c.* from Tb_Consumer c join Tb_Requests t1 on c.Con_ID=t1.Con_ID
    where not exists( select 1 from Tb_Requests t where c.Con_ID=t.Con_ID
                      and product_name='milk')

edited Apr 02 '19 at 19:20

answered Apr 02 '19 at 19:04

Zaynul Abadin Tuhin

31,407
5
33
63

No need for the `JOIN` to `Tb_Requests` here., as you're not returning any data in the `SELECT`. The `EXISTS` is enough. – Thom A Apr 02 '19 at 19:16
but there is a condition at least one product request you have done which will confirm request table join – Zaynul Abadin Tuhin Apr 02 '19 at 19:18
Yes, you're right. If they have multiple requests, then you would get duplicates though. – Thom A Apr 02 '19 at 19:19
1

Did make me realise mine is probably wrong. :) *(Now fixed too)* – Thom A Apr 02 '19 at 19:21
@GameGandhi555 OK, but we can't see your screen. :) – Thom A Apr 02 '19 at 19:23
I can see I didn't post anything lol, I get this error code Msg 245, Level 16, State 1, Line 25 Conversion failed when converting the varchar value 'milk' to data type int – GameGandhi555 Apr 02 '19 at 19:25
1

We don't know the name of your columns @GameGandhi555, we can't see your data, so Zayah has guessed. Use the column with the name of your product in the table `Tb_Requests`. Otherwise, replace the literal string `'Milk'` with the ID of the product `'Milk'`. I don't mean to offend, but you should try to solve simple problems like that one yourself or you'll never learn. – Thom A Apr 02 '19 at 19:28

score 1 · Answer 2 · answered Apr 02 '19 at 19:19

Rather than using EXISTS you could use HAVING. This'll save on 2 scans/seeks of the table tb_Requests.

SELECT C.Con_ID, C.[Name]
FROM Tb_Consumer C
     JOIN Tb_Requests R ON C.Con_ID = R.Con_ID
GROUP BY C.Con_ID, C.[Name]
HAVING COUNT(R.Req_ID) > 0
   AND COUNT(CASE R.Product_name WHEN 'Milk' THEN 1 END) = 0;

I need some help solving a SQL query, I need to list the names of consumers requesting at least one product but not requesting milk

2 Answers2