CODEWITHSUNDEEP
sqlsql-server
Mr.Bear
Mr.Bear

Reputation: 37

MS SQL Not Like Query

I have 2 tables, one a list of students and one with a list of restaurants. The people table has PK that is contained within the restaurants table as an FK. The restaurant table contains the days the students like to eat out at restaurants (along with restaurants they eat at). I'm trying to find the students who do not like to eat at the restaurant on Monday. I tried using the query below, but it lists all of the students and not the ones that don't eat out on Monday.

SELECT DISTINCT SName   
FROM Dinner AS D
INNER JOIN Student AS S ON D.SID = S.SID    
WHERE DinnerDay NOT LIKE 'Monday'
GROUP BY SName

Upvotes: 1

Views: 192

Answers (4)

Zorkolot
Zorkolot

Reputation: 2027

The Inner Join will remove rows that do not match on joining conditions. So you could have students that never eat out an any restaurant, as written the inner join will eliminate these students. Instead you should use a left join, which will return null.

You also did a DISTINCT with a GROUP BY. This is removing duplicates twice. You only need to do one of these.

SELECT DISTINCT CASE WHEN (D.ID IS NOT NULL)
                      THEN S.SName
                END
FROM Student AS S
LEFT JOIN Dinner AS D ON S.SID = D.SID AND D.DinnerDay = 'Monday'

This will return NULL for D.ID if the Student does not eat on Monday, or never eats out at any restaurant. The case statement checks if D.ID IS NOT NULL, if so it returns S.SName. This select is DISTINCT to remove duplicates.

Upvotes: 0

HoneyBadger
HoneyBadger

Reputation: 15150

I think you are looking for this:

SELECT  DISTINCT SName
FROM    Student AS S
WHERE   NOT EXISTS (
                        SELECT      NULL
                        FROM        Dinner AS D
                        WHERE       DinnerDay = 'Monday'
                                AND D.SID = S.SID 
                    )

Upvotes: 1

Lolu Omosewo
Lolu Omosewo

Reputation: 263

You can use student table and a subquery. The subquery is the distinct list of all students that dine on Monday, then run a left outerjoin of student and subquery b where b.SID is null;

select S.SName from Student as S 
Left Outer Join 
(select Distinct S2.SID from Student as S2 Inner Join Dinner as D on S2.SID=D.SID where D.DinnerDay='Monday') as tbMonStd
on S.SID=tbMonStd.SID
where tbMonStd.SID IS NULL

The Subquery gives you the list of all students attached to Monday. The left outerjoin and the last where clause gives you the students not found in Monday list

Upvotes: 1

Jr. Mediocre Coder
Jr. Mediocre Coder

Reputation: 84

SELECT DISTINCT SName   
FROM Dinner AS D
INNER JOIN Student AS S ON D.SID = S.SID    
WHERE DinnerDay <> 'Monday'

Try using <>

Upvotes: 0

Related Questions