multiple joins not working (1 Viewer)

[etk]

I have a query which essentially joins names and email addresses out of several tables. The query joins correctly if you do not include the final join and I can't figure out why.

SELECT tbl_Session.*, tbl_agent.Agent_Email, tbl_team_mgr.TM_Name, tbl_team_mgr.TM_Email, tbl_NHD.NHD_Name, tbl_NHD.NHD_Email
FROM (((tbl_Session 
INNER JOIN tbl_agent ON tbl_Session.SAP_ID = tbl_agent.SAP_ID) 
INNER JOIN tbl_team_mgr ON tbl_Session.TM_SAP_ID = tbl_team_mgr.TM_SAP_ID) 
INNER JOIN tbl_NHD ON tbl_Session.NHD_SAP_ID = tbl_NHD.NHD_SAP_ID)
WHERE tbl_Session.Status_ID=3;

I believe I have the parentheses needed for the joins, but for whatever reason it returns no results. It should return two results on my test. When I delete the final Inner Join and corresponding pieces in the Select statement it returns the two results that I expect, along with the joined fields. Can anyone suggest why I am experiencing this problem?

 

[GSSDevelopment]

When you run it with the INNER JOIN tbl_NHD, does it give you an error, or simply 0 results?

Shot in the dark, can you run it with LEFT JOIN tbl_NHD and let us know the results?


EDIT: It's possible that you have different data types in the tbl_Session table and the tbl_NHD table for the field NHD_SAP_ID. If that's the case, the JOIN criterion may not register the matches correctly.

LEFT JOIN should pull in all of the data from the previous set of JOINs, and will let you know if anything matched from tbl_NHD or not.

 

[etk]

Thanks. I was getting 0 records previously.

I ran the left join as you suggested on tbl_NHD and now I get the two records I expected, but the NHD fields are blank. I can assure you I have the IDs and records in the NHD table and the data types are the same (long integer on the NHD_SAP_ID in both tables). So, I guess I know now why the INNER Join was not working, but why is it not returning the fields from tbl_NHD?

 

[GSSDevelopment]

What are your results running the following?

SELECT tbl_Session.*, tbl_NHD.NHD_Name, tbl_NHD.NHD_Email
FROM (tbl_Session LEFT JOIN tbl_NHD ON tbl_Session.NHD_SAP_ID = tbl_NHD.NHD_SAP_ID)
WHERE tbl_Session.Status_ID=3;

 

[etk]

The same, both NHD_Name and NHD_Email return blank. The number of records is as expected, so the Where clause is opertating correctly.

 

[GSSDevelopment]

There WHERE clause was never in question. If the fields for NHD_Name and NHD_Email are blank, then your JOIN criterion is not matching.

Ensure that you have a matching NHD_SAP_ID row in your tbl_NHD and that it corresponds to the NHD_SAP_ID in tbl_Session.

 

[etk]

typo in one ID that I used exclusively for the test...

Public internet forums: anonymous embarassment since 1995!