CODEWITHSUNDEEP
mysqljoinsql-like
Neil M.
Neil M.

Reputation: 456

SQL Join with not like

I'm on MySQL 5.0.

I basically have a List Item table and a List Title table. For each Title, there can be 60+ items, which are all text. Dynamically I am trying to get Titles returned that have items people want to include or exclude. My Query is basically:

Select Distinct TitleID from Titles
left join Items on Items.titleID = Titles.titleID
Where Items.Name not like 'Item 2'

If any Items have Name like 'Item 2' then I don't want the TitleID. However there could be 59 other Names associated with a Title, so this query is still returning Every possible TitleID.

How can I write it so that I only get the TitleIDs I'm looking for?

Upvotes: 2

Views: 2285

Answers (2)

Philip Sheard
Philip Sheard

Reputation: 5825

Select Distinct TitleID from Items
Where TitleID not in (
Select TitleID from Items
Where Name like 'Item 2')

There is no wildcard in 'item 2' though, so it does not make much sense.

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

SELECT t.TitleID
    FROM Titles t
    WHERE NOT EXISTS(SELECT NULL 
                         FROM Items i 
                         WHERE i.TitleID = t.TitleID 
                             AND i.Name LIKE 'Item 2%')

Upvotes: 0

Related Questions