CODEWITHSUNDEEP
mysqlsqldatabase
I'll-Be-Back
I'll-Be-Back

Reputation: 10828

Find Record if value does not exist?

I am having problem with the SQL Query.

I want to find StatusID = 1 in the records table IF StatusID = 2 does not exist.

I have tried the query: 

SELECT * FROM records AS A
   LEFT JOIN records AS B on B.StoreID = A.StoreID
WHERE A.StatusID = 1 AND B.StatusID != 2

It is still showing the result even if StatusID = 2 is exist.

Note: StoreID are the ref id in the records table.

Upvotes: 2

Views: 8584

Answers (3)

Chris
Chris

Reputation: 1702

You're still seeing the result even if StatusID = 2 exists because you're using a LEFT JOIN, which as you'll remember returns the entire left set and all matching entries from the right set or a NULL where none exist.

Seems to me the easiest fix is to just use an INNER JOIN, since for the cases LEFT JOIN will return that INNER JOIN won't, StatusID will equal neither 1 nor 2.

Thus:

select * from records a
inner join records b
on b.storeid = a.storeid
where a.statusid = 1
and b.statusid <> 2

Upvotes: 0

MarianP
MarianP

Reputation: 2759

Assumming non-null StatusID field (is always filled in):

SELECT * FROM records AS A
   LEFT JOIN records AS B 
    on B.StoreID = A.StoreID
     and B.StatusID = 2
WHERE A.StatusID = 1 AND B.StatusID IS NULL

Upvotes: 0

Adriano Carneiro
Adriano Carneiro

Reputation: 58635

You want to use NOT EXISTS:

SELECT * 
FROM records AS A
WHERE A.StatusID = 1 
AND NOT EXISTS (select B.StoreID 
                from records AS B 
                where B.StoreID = A.StoreIDB 
                and B.StatusID = 2)

Upvotes: 8

Related Questions