Solved

Limiting data returned from MySQL Database

Posted on 2009-04-14
2
225 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:dannyg280
2 Comments
 
LVL 1

Accepted Solution

by:
qes27 earned 500 total points
ID: 24140393
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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question