I have 2 tables in a query using a left join because sometimes the field I'm joining on in Table A has null values. That returns the data I'm expecting but I need to add a where clause to Table B and once I do that I don't get any data. Since it's a left join I'm confused as to why I'm no longer getting Table A's fields. Probablly a really simple answer to this but it's evading me.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Left Join with Where clause not working
- Thread starter KimmieB
- Start date
traingamer
Programmer
Try posting your sql. Otherwise, we'd just be guessing.
- Thread starter
- #4
Here's my sql:
SELECT tbl_loan_review_rem.LOAN_ID, tbl_loan_review_rem.REQUESTOR_FIRST_NAME, tbl_loan_review_rem.REQUESTOR_LAST_NAME, tbl_loan_review_rem.REQUESTOR_COMPANY, tbl_QCREM_list_table.VALUE_DESC AS [Loan Status]
FROM tbl_loan_review_rem LEFT JOIN tbl_QCREM_list_table ON tbl_loan_review_rem.MI_STATUS = tbl_QCREM_list_table.LIST_VALUE
WHERE (((tbl_QCREM_list_table.LIST_ID)=170))
GROUP BY tbl_loan_review_rem.LOAN_ID, tbl_loan_review_rem.REQUESTOR_FIRST_NAME, tbl_loan_review_rem.REQUESTOR_LAST_NAME, tbl_loan_review_rem.REQUESTOR_COMPANY, tbl_QCREM_list_table.VALUE_DESC
HAVING (((tbl_loan_review_rem.LOAN_ID)="ALB2599490"));
It's the "WHERE (((tbl_QCREM_list_table.LIST_ID)=170))" stmt that is causing the problems.
SELECT tbl_loan_review_rem.LOAN_ID, tbl_loan_review_rem.REQUESTOR_FIRST_NAME, tbl_loan_review_rem.REQUESTOR_LAST_NAME, tbl_loan_review_rem.REQUESTOR_COMPANY, tbl_QCREM_list_table.VALUE_DESC AS [Loan Status]
FROM tbl_loan_review_rem LEFT JOIN tbl_QCREM_list_table ON tbl_loan_review_rem.MI_STATUS = tbl_QCREM_list_table.LIST_VALUE
WHERE (((tbl_QCREM_list_table.LIST_ID)=170))
GROUP BY tbl_loan_review_rem.LOAN_ID, tbl_loan_review_rem.REQUESTOR_FIRST_NAME, tbl_loan_review_rem.REQUESTOR_LAST_NAME, tbl_loan_review_rem.REQUESTOR_COMPANY, tbl_QCREM_list_table.VALUE_DESC
HAVING (((tbl_loan_review_rem.LOAN_ID)="ALB2599490"));
It's the "WHERE (((tbl_QCREM_list_table.LIST_ID)=170))" stmt that is causing the problems.
- Thread starter
- #6
Posted at the same time than KimmieB...
you may try this:
WHERE tbl_loan_review_rem.LOAN_ID="ALB2599490"
AND (tbl_QCREM_list_table.LIST_ID=170 OR tbl_QCREM_list_table.LIST_ID Is Null)
GROUP BY tbl_loan_review_rem.LOAN_ID, tbl_loan_review_rem.REQUESTOR_FIRST_NAME, tbl_loan_review_rem.REQUESTOR_LAST_NAME, tbl_loan_review_rem.REQUESTOR_COMPANY, tbl_QCREM_list_table.VALUE_DESC;
If still unexpexted result, create a save query named, say, qryLIST_ID170:
SELECT * from tbl_QCREM_list_table WHERE LIST_ID = 170;
and then do the LEFT JOIN on this query.
Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
you may try this:
WHERE tbl_loan_review_rem.LOAN_ID="ALB2599490"
AND (tbl_QCREM_list_table.LIST_ID=170 OR tbl_QCREM_list_table.LIST_ID Is Null)
GROUP BY tbl_loan_review_rem.LOAN_ID, tbl_loan_review_rem.REQUESTOR_FIRST_NAME, tbl_loan_review_rem.REQUESTOR_LAST_NAME, tbl_loan_review_rem.REQUESTOR_COMPANY, tbl_QCREM_list_table.VALUE_DESC;
If still unexpexted result, create a save query named, say, qryLIST_ID170:
SELECT * from tbl_QCREM_list_table WHERE LIST_ID = 170;
and then do the LEFT JOIN on this query.
Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
FROM tbl_loan_review_rem LEFT JOIN tbl_QCREM_list_table ON tbl_loan_review_rem.MI_STATUS = tbl_QCREM_list_table.LIST_VALUE
WHERE (((tbl_QCREM_list_table.LIST_ID)=170))
Since the LIST_ID = 170 is applied on the right joined table this effectively eliminates all the null records in the right table, which is all the unequal records. You can do as PHV suggests that is adding Or LIST_ID is null to the criteria which will bring back all the unequal records. Another way is to apply the criteria in the join instead.
LEFT JOIN tbl_QCREM_list_table ON tbl_loan_review_rem.MI_STATUS = tbl_QCREM_list_table.LIST_VALUE
AND tbl_QCREM_list_table.LIST_ID = 170
Since this is done at the JOIN the records from the Left table will still be available.
WHERE (((tbl_QCREM_list_table.LIST_ID)=170))
Since the LIST_ID = 170 is applied on the right joined table this effectively eliminates all the null records in the right table, which is all the unequal records. You can do as PHV suggests that is adding Or LIST_ID is null to the criteria which will bring back all the unequal records. Another way is to apply the criteria in the join instead.
LEFT JOIN tbl_QCREM_list_table ON tbl_loan_review_rem.MI_STATUS = tbl_QCREM_list_table.LIST_VALUE
AND tbl_QCREM_list_table.LIST_ID = 170
Since this is done at the JOIN the records from the Left table will still be available.
- Status
Similar threads
- Locked
- Question
- Locked
- Question