CODEWITHSUNDEEP
sqlms-accessjoinms-access-2010left-join
jpford
jpford

Reputation: 83

Join Statements in Access SQL

I'm new with SQL and how it works with Access, and I had a problem with a query only returning items from a specific set.

So according to the definitions I read a LEFT JOIN statement is supposed to return all records from the Left table then only specific records from the Right table. For some reason it will not return all records from my left table. Please see code below. It still somehow wants to only return records that have some kind of relationship. Any Ideas? I want all records from my left table ( tbl_historicOrg) to be returned then only specific records from the Right table.

SELECT tbl_historicOrg.NAME, tbl_historicOrg.Racf, tbl_historicOrg.STATUS, tbl_historicOrg.PCF, tbl_historicOrg.[Date Finalized], tbl_historicOrg.[Hist Month], import_data.SCHDLE_EXCPT_NM, import_data.HOURS
FROM tbl_historicOrg LEFT JOIN import_data ON tbl_historicOrg.Racf = import_data.RACF
WHERE (((import_data.SCHDLE_EXCPT_NM)="System Problems"));

Any suggestions or corrections would be greatly appreciated.

Upvotes: 0

Views: 102

Answers (2)

Rahul
Rahul

Reputation: 77846

Try like below; get the filtered data and do a JOIN with that result set

SELECT tbl_historicOrg.NAME, 
tbl_historicOrg.Racf, 
tbl_historicOrg.STATUS, 
tbl_historicOrg.PCF, 
tbl_historicOrg.[Date Finalized], 
tbl_historicOrg.[Hist Month],
tab.SCHDLE_EXCPT_NM, 
tab.HOURS 
FROM tbl_historicOrg 
LEFT JOIN (
select SCHDLE_EXCPT_NM, 
       HOURS,
       RACF
from import_data where SCHDLE_EXCPT_NM = "System Problems" ) as tab
ON tbl_historicOrg.Racf = tab.RACF;

Upvotes: 1

andy
andy

Reputation: 6079

Your query should be like below, Dont use 'WHERE' clause for second table, its filtering the data for 'import_data.SCHDLE_EXCPT_NM = "System Problems"'

SELECT tbl_historicOrg.NAME, tbl_historicOrg.Racf, tbl_historicOrg.STATUS, 
tbl_historicOrg.PCF, tbl_historicOrg.[Date Finalized], tbl_historicOrg.[Hist Month], 
import_data.SCHDLE_EXCPT_NM, import_data.HOURS
FROM tbl_historicOrg LEFT JOIN import_data ON tbl_historicOrg.Racf = import_data.RACF

Upvotes: 0

Related Questions