Solved

Limiting data returned from MySQL Database

Posted on 2009-04-14
2
229 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 39
SQL SELECT query help 7 56
CSV How to add columns based on existing column(s)? 20 32
help restore my wordpress site made on WAMP 8 23
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
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…

792 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