Use INNER JOINs or LEFT JOINs

jrdnoland · Sep 17, 2010

I have an access db that is used to return sql querys from excel. What I have below works but it doesn't always return all the records.

Should I be using all LEFT Joins? I want to return all the records from all the tables where the key field (Stock_Code) matches.

Code:

SQLSTRING = SQLSTRING & ITEMSA
SQLSTRING = SQLSTRING & " FROM ((((((((((((tblGeneral_InformationSecondary "
SQLSTRING = SQLSTRING & " INNER JOIN tblCertificationsSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblCertificationsSecondary.P_Certifications_Stock_Code) "
SQLSTRING = SQLSTRING & " LEFT JOIN tblEC_NumbersSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblEC_NumbersSecondary.P_EC_Stock_Code) "
SQLSTRING = SQLSTRING & " LEFT JOIN tblCurrent_UserSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblCurrent_UserSecondary.P_C_Stock_Code) "
SQLSTRING = SQLSTRING & " INNER JOIN tblGradeSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblGradeSecondary.P_Grade_Stock_Code) "
SQLSTRING = SQLSTRING & " INNER JOIN tblINCI_CASSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblINCI_CASSecondary.P_CAS_Stock_Code) "
SQLSTRING = SQLSTRING & " LEFT JOIN tblINCI_NamesSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblINCI_NamesSecondary.P_INCI_Stock_Code) "
SQLSTRING = SQLSTRING & " INNER JOIN tblIncoming_TestsSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblIncoming_TestsSecondary.P_Incoming_Stock_Code) "
SQLSTRING = SQLSTRING & " INNER JOIN tblManufacturersSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblManufacturersSecondary.P_Man_Stock_Code) "
SQLSTRING = SQLSTRING & " INNER JOIN tblPPESecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblPPESecondary.P_PPE_Stock_Code) "
SQLSTRING = SQLSTRING & " LEFT JOIN tblREACH_NumbersSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblREACH_NumbersSecondary.P_REACH_Stock_Code) "
SQLSTRING = SQLSTRING & " INNER JOIN tblRevisionHistorySecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblRevisionHistorySecondary.P_Revision_History_Stock_Code) "
SQLSTRING = SQLSTRING & " INNER JOIN tblTypeSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblTypeSecondary.P_Type_Stock_Code) "
SQLSTRING = SQLSTRING & " LEFT JOIN tblPurchase_SpecSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblPurchase_SpecSecondary.P_Purchasing_Stock_Code "

namliam · Sep 17, 2010

If you always want to see your data from tblGeneral_InformationSecondary then yes all left joins....

Now how much easier would that be to read if it all looked like:

Code:

SQLSTRING = SQLSTRING & " FROM ((((((((((((tblGeneral_InformationSecondary "
SQLSTRING = SQLSTRING & " INNER JOIN tblCertificationsSecondary            ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblCertificationsSecondary.P_Certifications_Stock_Code) "
SQLSTRING = SQLSTRING & " LEFT  JOIN tblEC_NumbersSecondary                ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblEC_NumbersSecondary.P_EC_Stock_Code) "

jrdnoland · Sep 17, 2010

Thank you, I want to see all the data from all the tables as long as the Stock_Code is the same. So I guess I'll try all LEFT JOINs and see what happens.

jrdnoland · Nov 11, 2010

I had to add another table to this DB. It was working fine until some duplicate records were added to the DB. By duplicate, I mean that several revisions of the Stock_Code number have been added, e.g. originally all records were like :
Stock Code Revision
0800-001 1
0800-005 6

Now, the same Stock Code may have several revision numbers:
Stock Code Revision
0800-001 1
0800-001 2
0800-005 6
0800-005 7

This now hangs the query, if I remove the additional revision numbers then it works again.

Is there another approach, other than putting the addition revisions in different tables?

Code:

 [FONT=&quot]SELECT DISTINCT tblGeneral_InformationSecondary.P_Gen_Stock_Code AS [Stock Code] FROM tblPurchase_SpecSecondary
 RIGHT JOIN (((((((((((tblGeneral_InformationSecondary 
 LEFT JOIN tblINCI_NamesSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblINCI_NamesSecondary.P_INCI_Stock_Code)  
LEFT JOIN tblINCI_CASSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblINCI_CASSecondary.P_CAS_Stock_Code)  
LEFT JOIN tblEC_NumbersSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblEC_NumbersSecondary.P_EC_Stock_Code)  
LEFT JOIN tblREACH_NumbersSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblREACH_NumbersSecondary.P_REACH_Stock_Code)  
LEFT JOIN tblGradeSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblGradeSecondary.P_Grade_Stock_Code)  
LEFT JOIN tblTypeSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblTypeSecondary.P_Type_Stock_Code)  
LEFT JOIN tblCertificationsSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblCertificationsSecondary.P_Certifications_Stock_Code)  
LEFT JOIN tblManufacturersSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblManufacturersSecondary.P_Man_Stock_Code) 
 LEFT JOIN tblIncoming_TestsSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblIncoming_TestsSecondary.P_Incoming_Stock_Code)  
LEFT JOIN tblPPESecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblPPESecondary.P_PPE_Stock_Code)  
LEFT JOIN tblRevisionHistorySecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblRevisionHistorySecondary.P_Revision_History_Stock_Code)  ON tblPurchase_SpecSecondary.P_Purchasing_Stock_Code = tblGeneral_InformationSecondary.P_Gen_Stock_Code  
WHERE tblGeneral_InformationSecondary.P_Gen_Stock_Code >'0193-184';[/FONT]

namliam · Nov 11, 2010

Seeing as your not using any of the other tables how about simply:

Code:

SELECT DISTINCT tblGeneral_InformationSecondary.P_Gen_Stock_Code AS [Stock Code] FROM tblPurchase_SpecSecondary
WHERE tblGeneral_InformationSecondary.P_Gen_Stock_Code >'0193-184';

jrdnoland · Nov 11, 2010

namliam said:
Seeing as your not using any of the other tables how about simply:

Code:

SELECT DISTINCT tblGeneral_InformationSecondary.P_Gen_Stock_Code AS [Stock Code] FROM tblPurchase_SpecSecondary WHERE tblGeneral_InformationSecondary.P_Gen_Stock_Code >'0193-184';

Thanks for looking at this again.

What I posted was only an example of a query that may be run. The reason all the tables are joined is that this sql is generated by users picking fields and parameters from and list and then combo box.

Those fields and values are then joined to build the sql string which is then passed to a function that runs the query and populates a excel worksheet with the values.

Heres another example:

Code:

[FONT=Times New Roman][FONT=Arial]SELECT DISTINCT tblGeneral_InformationSecondary.P_Gen_Stock_Code AS [Stock Code],tblGradeSecondary.P_Grade_Description AS [Grade],tblTypeSecondary.P_Type_Description AS [Type],tblGeneral_InformationSecondary.P_Gen_Function AS [Function],tblGeneral_InformationSecondary.P_Gen_SubFunction AS [Sub-Function],tblGeneral_InformationSecondary.P_Gen_Preservation AS [Preservation],tblGeneral_InformationSecondary.P_Gen_Irradiation AS [Irradiation] FROM tblPurchase_SpecSecondary [/FONT][/FONT]
 
[FONT=Times New Roman][FONT=Arial]RIGHT JOIN (((((((((((tblGeneral_InformationSecondary  [/FONT][/FONT]
 
[FONT=Times New Roman][FONT=Arial]LEFT JOIN tblINCI_NamesSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblINCI_NamesSecondary.P_INCI_Stock_Code)  [/FONT][/FONT]
 
[FONT=Times New Roman][FONT=Arial]LEFT JOIN tblINCI_CASSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblINCI_CASSecondary.P_CAS_Stock_Code)  [/FONT][/FONT]
 
[FONT=Times New Roman][FONT=Arial]LEFT JOIN tblEC_NumbersSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblEC_NumbersSecondary.P_EC_Stock_Code)  [/FONT][/FONT]
 
[FONT=Times New Roman][FONT=Arial]LEFT JOIN tblREACH_NumbersSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblREACH_NumbersSecondary.P_REACH_Stock_Code) [/FONT][/FONT]
 
[FONT=Times New Roman][FONT=Arial]LEFT JOIN tblGradeSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblGradeSecondary.P_Grade_Stock_Code)  [/FONT][/FONT]
 
[FONT=Times New Roman][FONT=Arial]LEFT JOIN tblTypeSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblTypeSecondary.P_Type_Stock_Code)  [/FONT][/FONT]
 
[FONT=Times New Roman][FONT=Arial]LEFT JOIN tblCertificationsSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblCertificationsSecondary.P_Certifications_Stock_Code)  [/FONT][/FONT]
 
[FONT=Times New Roman][FONT=Arial]LEFT JOIN tblManufacturersSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblManufacturersSecondary.P_Man_Stock_Code)  [/FONT][/FONT]
 
[FONT=Times New Roman][FONT=Arial]LEFT JOIN tblIncoming_TestsSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblIncoming_TestsSecondary.P_Incoming_Stock_Code) [/FONT][/FONT]
 
[FONT=Times New Roman][FONT=Arial]LEFT JOIN tblPPESecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblPPESecondary.P_PPE_Stock_Code)  [/FONT][/FONT]
 
[FONT=Times New Roman][FONT=Arial]LEFT JOIN tblRevisionHistorySecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblRevisionHistorySecondary.P_Revision_History_Stock_Code)  [/FONT][/FONT]
 
[FONT=Times New Roman][FONT=Arial]ON tblPurchase_SpecSecondary.P_Purchasing_Stock_Code = tblGeneral_InformationSecondary.P_Gen_Stock_Code  [/FONT][/FONT]
 
[FONT=Times New Roman][FONT=Arial]WHERE tblGeneral_InformationSecondary.P_Gen_Function='Surfactant';[/FONT][/FONT]

The Stock Code Field along with another field (revision number) are primary fields in each table

namliam · Nov 11, 2010

You should then join (I think) also on your revision number... i.e.

Code:

tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblINCI_NamesSecondary.P_INCI_Stock_Code AND tblGeneral_InformationSecondary.Revision = tblINCI_NamesSecondary.Revision

And do so for each table...

jrdnoland · Nov 11, 2010

namliam said:
You should then join (I think) also on your revision number... i.e.

Code:

tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblINCI_NamesSecondary.P_INCI_Stock_Code AND tblGeneral_InformationSecondary.Revision = tblINCI_NamesSecondary.Revision

And do so for each table...

I actually thought of that and when I try it I get an error message stating that the SQl statement cound not be executed becasue it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement.

The problem with that is that the first join is what I'm trying to run and it locks up the computer.

namliam · Nov 12, 2010

If your not getting that error on the original query there is no reason for it to happen on the altered query... Unless your doing something wrong to it....

the "AND tblGeneral_InformationSecondary.Revision = tblINCI_NamesSecondary.Revision"
Should be read as "AND tblGeneral_InformationSecondary.Revision = THISTABLE.Revision"

jrdnoland · Nov 12, 2010

namliam said:
If your not getting that error on the original query there is no reason for it to happen on the altered query... Unless your doing something wrong to it....

the "AND tblGeneral_InformationSecondary.Revision = tblINCI_NamesSecondary.Revision"
Should be read as "AND tblGeneral_InformationSecondary.Revision = THISTABLE.Revision"

My first attempt at doing that was to copy the sql string into access and then manually add the second join, that's when I got the error messages.

After you saying it should work I just wrote it into the string as:

Code:

SELECT DISTINCT tblGeneral_InformationSecondary.P_Gen_Stock_Code AS [Stock Code],tblGradeSecondary.P_Grade_Description AS [Grade],tblTypeSecondary.P_Type_Description AS [Type],tblGeneral_InformationSecondary.P_Gen_Function AS [Function],tblGeneral_InformationSecondary.P_Gen_SubFunction AS [Sub-Function],tblGeneral_InformationSecondary.P_Gen_Preservation AS [Preservation],tblGeneral_InformationSecondary.P_Gen_Irradiation AS [Irradiation] FROM tblPurchase_SpecSecondary 
 
RIGHT JOIN (((((((((((tblGeneral_InformationSecondary  
 
LEFT JOIN tblINCI_NamesSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblINCI_NamesSecondary.P_INCI_Stock_Code AND tblGeneral_InformationSecondary.P_Gen_Numb_Revisions = tblINCI_NamesSecondary.P_INCI_Numb_Revisions)  
 
LEFT JOIN tblINCI_CASSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblINCI_CASSecondary.P_CAS_Stock_Code AND tblGeneral_InformationSecondary.P_Gen_Numb_Revisions = tblINCI_CASSecondary.P_CAS_Numb_Revisions)  
 
LEFT JOIN tblEC_NumbersSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblEC_NumbersSecondary.P_EC_Stock_Code AND  tblGeneral_InformationSecondary.P_Gen_Numb_Revisions = tblEC_NumbersSecondary.P_EC_Numb_Revisions)  
 
LEFT JOIN tblREACH_NumbersSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblREACH_NumbersSecondary.P_REACH_Stock_Code AND tblGeneral_InformationSecondary.P_Gen_Numb_Revisions = tblREACH_NumbersSecondary.P_REACH_Numb_Revisions) 
 
LEFT JOIN tblGradeSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblGradeSecondary.P_Grade_Stock_Code AND tblGeneral_InformationSecondary.P_Gen_Numb_Revisions = tblGradeSecondary.P_Grade_Numb_Revisions)  
 
LEFT JOIN tblTypeSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblTypeSecondary.P_Type_Stock_Code AND  tblGeneral_InformationSecondary.P_Gen_Numb_Revisions = tblTypeSecondary.P_Type_Numb_Revisions)  
 
LEFT JOIN tblCertificationsSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblCertificationsSecondary.P_Certifications_Stock_Code AND tblGeneral_InformationSecondary.P_Gen_Numb_Revisions = tblCertificationsSecondary.P_Certifications_Numb_Revisions)  
 
LEFT JOIN tblManufacturersSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblManufacturersSecondary.P_Man_Stock_Code AND tblGeneral_InformationSecondary.P_Gen_Numb_Revisions = tblManufacturersSecondary.P_Man_Numb_Revisions)  
 
LEFT JOIN tblIncoming_TestsSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblIncoming_TestsSecondary.P_Incoming_Stock_Code AND tblGeneral_InformationSecondary.P_Gen_Numb_Revisions = tblIncoming_TestsSecondary.P_Incoming_Numb_Revisions) 
 
LEFT JOIN tblPPESecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblPPESecondary.P_PPE_Stock_Code AND  tblGeneral_InformationSecondary.P_Gen_Numb_Revisions = tblPPESecondary.P_PPE_Numb_Revisions)  
 
LEFT JOIN tblRevisionHistorySecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code = tblRevisionHistorySecondary.P_Revision_History_Stock_Code AND tblGeneral_InformationSecondary.P_Gen_Numb_Revisions = tblRevisionHistorySecondary.P_Revision_Numb_Revisions)  
 
ON tblPurchase_SpecSecondary.P_Purchasing_Stock_Code = tblGeneral_InformationSecondary.P_Gen_Stock_Code  AND tblPurchase_SpecSecondary.P_Purchasing_Numb_Revisions = tblGeneral_InformationSecondary.P_Gen_Numb_Revisions  
 
WHERE tblGeneral_InformationSecondary.P_Gen_Function='Surfactant';

and it worked! I must have been doing something wrong in my first attempt. Thank you for your persistence!

namliam · Nov 12, 2010

Just to promote readablity if your not using the designer...

Code:

FROM tblPurchase_SpecSecondary 
 
RIGHT JOIN (((((((((((tblGeneral_InformationSecondary  
 
LEFT JOIN tblINCI_NamesSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code     = tblINCI_NamesSecondary.P_INCI_Stock_Code 
                                AND tblGeneral_InformationSecondary.P_Gen_Numb_Revisions = tblINCI_NamesSecondary.P_INCI_Numb_Revisions)  
LEFT JOIN tblINCI_CASSecondary   ON tblGeneral_InformationSecondary.P_Gen_Stock_Code     = tblINCI_CASSecondary.P_CAS_Stock_Code  
                                AND tblGeneral_InformationSecondary.P_Gen_Numb_Revisions = tblINCI_CASSecondary.P_CAS_Numb_Revisions   )  
LEFT JOIN tblEC_NumbersSecondary ON tblGeneral_InformationSecondary.P_Gen_Stock_Code     = tblEC_NumbersSecondary.P_EC_Stock_Code 
                                AND tblGeneral_InformationSecondary.P_Gen_Numb_Revisions = tblEC_NumbersSecondary.P_EC_Numb_Revisions  )

Use INNER JOINs or LEFT JOINs

jrdnoland

Registered User.

namliam

The Mailman - AWF VIP

jrdnoland

Registered User.

jrdnoland

Registered User.

namliam

The Mailman - AWF VIP

jrdnoland

Registered User.

namliam

The Mailman - AWF VIP

jrdnoland

Registered User.

namliam

The Mailman - AWF VIP

jrdnoland

Registered User.

namliam

The Mailman - AWF VIP

Similar threads

Users who are viewing this thread