Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Limiting data returned from MySQL Database

Posted on 2009-04-14
2
Medium Priority
?
246 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 2000 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

618 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