Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

Need Query to pull data from 2 tables with where clause

I work for a company that does audit reports on the cleanliness of stores. I have 2 tables
HanStores and HanAR. HanStores contains data for every store in a chain, such as store location, name etc. HanAR contains all the data from each audit report. I need a query to list out every store (wether or not it has an audit report in the database), and if it does I need to list the data etc of the lastest report. So I need data from both tables. The following query worked fine:

"SELECT
T.`StoreNum` As StoreNum,
T.DistNum As DistNum,
T.Address1 As Address1,
T.City As City,
T.State As State,
T1.DateTimeEntered As DateTimeEntered,
T1.Baseline As Baseline
From HanStores T
Left Join HanAR T1 On T.`StoreNum` = T1.StoreID
order by StoreNum Asc;";

T1.Baseline is a field that has a 'Y' or 'N' that indicates whether it is a baseline report... when I try to run the same query with a where clause of "Where T1.Baseline !='Y'" it limits the output to only stores that have audit reports which are not baseline... but I do not get the data from HanStores unless there is an entery in HanAR for that store...
I'm not sure if I have to do a different join or a subquery to get what I need...
0
dannyg280
Asked:
dannyg280
1 Solution
 
TextReportCommented:
With the left outer join the field will be null so you need to include a check for this.
Cheers, Andrew
SELECT T.StoreNum As StoreNum
     , T.DistNum As DistNum
     , T.Address1 As Address1
     , T.City As City
     , T.State As State
     , T1.DateTimeEntered As DateTimeEntered
     , T1.Baseline As Baseline
From HanStores T
   Left Join HanAR T1 On T.StoreNum = T1.StoreID
Where T1.Baseline !='Y' OR T1.Baseline IS NULL
ORDER BY StoreNum Asc

Open in new window

0
 
ollyatstithiansCommented:
Looks like a NULL issue to me. Try adding another condition to your where clause to test for NULL.
Something like

...
where T1.Baseline = 'N' or T1.Baseline is null
...

The reason being that for rows where T1 is not there, all T1 fields will be NULL.

Olly.
0
 
dannyg280Author Commented:
Thanks! For some reason I thought just using a not equal would include NULL as well... Now I know... Thanks again!
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now