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:
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...