Limiting data returned from MySQL Database

I work for a company that does audit reports for store chains. I have a MySQL database with 2 tables one with store contact info (HanStores) and one with the audit report data (HanAR). I need to write a query to return store data from HanStores for each store that does not have an audit report in HanAR. The problem I am having is that some of the audit reports are "Baseline" audits and do not count as real audits... and some facilties have multiple audits. The query below works great but it returns all stores that have baseline audits... many of the stores have since had full audits and thus should not be included.. but I do not know how to mondify the query to tell it to not return the data if the same store has both a baseline and a real audit.
I beleive what I'm trying to do if fairly simple, so if my description is confusing please ask..
SELECT T.`StoreNum` , T.`StoreName` , T.`Address1` AS Address, T.`City` , T.`State` 
FROM HanStores T
LEFT JOIN HanAR T1 ON T.StoreNum = T1.StoreID
WHERE T.ActiveStore = 'Y'
AND (
T1.Baseline IS NULL 
OR T1.Baseline = 'Y'
)
AND T.AuditCompany = ''GenAudit'

Open in new window

dannyg280Asked:
Who is Participating?
 
qes27Connect With a Mentor Commented:
I often use EXISTS subqueries in the WHERE clause, though this can sometimes be a performance issue, it often lends itself to straight forward queries.

For example, you could add to your query above to filter out stores that have a "real" audit (my condition may not fit your actual schema, but the principal should work, assuming query execution performance is acceptable).
SELECT T.`StoreNum` , T.`StoreName` , T.`Address1` AS Address, T.`City` , T.`State` 
FROM HanStores T
LEFT JOIN HanAR T1 ON T.StoreNum = T1.StoreID
WHERE T.ActiveStore = 'Y'
AND (
T1.Baseline IS NULL 
OR T1.Baseline = 'Y'
)
AND T.AuditCompany = ''GenAudit'
 
AND NOT EXISTS(SELECT * FROM HanAR T2 WHERE T2.StoreID=T.StoreNum AND t.AuditCompany= ''RealAudit''

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.