Solved

Limiting data returned from MySQL Database

Posted on 2009-04-14
2
232 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
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…

726 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