Unmatched Query With Condition

[tcjones]

Hi there, I currently have the below tables:

tbl_stores
ID NAME
123 Store A
456 Store B

tbl_returns
ID NAME RETURN
123 Store A 9999
123 Store A 8888
789 Store C 9999

I want to find the stores that exist in the stores table, but not in the returns table WHERE the RETURN is equal to 9999. This would hopefully return 456 Store B

My problem is when I put a condition in my query it returns nothing as the condition is based on the left joined table.

SQL
SELECT tbl_stores.store, tbl_stores.name
FROM tbl_stores LEFT JOIN tbl_returns ON tbl_stores.store = tbl_returns.store
WHERE tbl_returns.store Is Null AND tbl_returns.survey_id=2
GROUP BY tbl_stores.store, tbl_stores.name, tbl_returns.store

Any ideas? Thanks for your help with this one!

Cheers

Tony

 

[gromit]

Hi Tony - Welcome!

I like to use the IN clause and NOT IN for this sorts of things:

SELECT * FROM tblStores WHERE tblStores.ID NOT IN (SELECT tblReturns.ID FROM tblReturns WHERE tblReturns.Return = 9999)

This first creates a sub-query with a list of all ID where Return = 9999. Then the main query returns all ID that are NOT in the first list.

hope that helps,

- gromit

 

[tcjones]

Legend.

That worked, thanks for your help champ

Tony