Trouble with joins in SQL query

[thomasks]

I have an SQL string that I am using to populate a grid in VB 6 (from an access 2003 .mdb) with the user having the ability to filter the grid based on the values in some text boxes. (these are the string variables at the bottom of the code). I am having a problem with one area of the statement. Here is the code:

SELECT Quote.Quote_ID, Project.Project_Number, Quote.ELM_version, Customer.Customer_Name, Site.Site_Location, Quote.Scope, Quote.Quote_Due_sales, Quote.Quote_complete, Quote.Quote_Total, Quote.Comments, PDM.PDM_LastName, Quote.RFQ_Sent_PM_Date, PM.PM_LastName, PM.PM_Hours, PM.PM_Travel, Quote.PM_Quote_Total, Quote.PM_Quote_Complete, Quote.RFQ_sent_SYS_date, Systems_Engineering.Sys_Estimator_LastName, Systems_Engineering.Sys_Hardware, Systems_Engineering.Sys_Resale, Systems_Engineering.Sys_Travel, Systems_Engineering.Sys_Hours, Systems_Engineering.Equip_list_done, Systems_Engineering.Dwgs_done, Systems_Engineering.Sys_Quote_Complete, Quote.Sys_Quote_Total, Quote.RFQ_sent_Ctls_date, Controls.Ctls_Estimator_LastName, Controls.Ctls_Hardware, Controls.Ctls_Tranships, Controls.Ctls_Resale, Controls.Ctls_Travel, Controls.Ctls_Hours, Quote.Ctls_Quote_Total, Controls.Ctls_Quote_Complete, Quote.RFQ_sent_SAE_date, SAE.SAE_Estimator_LastName, SAE.SAE_Hours, SAE.SAE_Hardware, SAE.SAE_Resale, SAE.SAE_Travel, Quote.SAE_Quote_Total, Quote.SAE_Quote_Complete, Quote.RFQ_sent_Mech_Inst_date, Mechanical_Installation.Mech_Inst_Estimator_LastName, Quote.Mech_Inst_Quote_Total, Quote.Mech_Inst_Quote_Complete, Quote.RFQ_sent_Elec_Inst_date, Electrical_Installation.Elec_Inst_Estimator_lName, Quote.Elec_Inst_Quote_Total, Quote.Elec_Inst_Quote_Complete, Quote.RFQ_sent_ISE_date, Quote.ISE_Estimator, Quote.ISE_Quote_Complete, Site.DC_Num, Quote.Date_Rcvd, Quote.Quote_Type, Quote.Base_CheckBox, Quote.Alt_CheckBox, Quote.CCQR_CheckBox, Quote.New_DC_CheckBox, Quote.Retro_CheckBox, Quote.Enhance_CheckBox
[COLOR=red]FROM Site INNER JOIN [/color red] (((((((((Quote LEFT JOIN Controls ON Quote.Quote_ID = Controls.Quote_ID) [COLOR=red]INNER JOIN Customer ON Quote.Quote_ID = Customer.Quote_ID)[/color red] LEFT JOIN Electrical_Installation ON Quote.Quote_ID = Electrical_Installation.Quote_ID) LEFT JOIN Mechanical_Installation ON Quote.Quote_ID = Mechanical_Installation.Quote_ID) LEFT JOIN PDM ON Quote.Quote_ID = PDM.Quote_ID) LEFT JOIN PM ON Quote.Quote_ID = PM.Quote_ID) LEFT JOIN Project ON Quote.Quote_ID = Project.Quote_ID) LEFT JOIN SAE ON Quote.Quote_ID = SAE.Quote_ID) LEFT JOIN Systems_Engineering ON Quote.Quote_ID = Systems_Engineering.Quote_ID)[COLOR=red] ON Site.Site_ID = Customer.Site_ID[/color red]
WHERE (((Project.Project_Number) ALike '” & sProject1 & “') AND ((Quote.ELM_version) ALike '” & sRev1 & “') AND ((Customer.Customer_Name) ALike '” & sCustomer1 & “') AND ((Quote.Scope) ALike '” & sScope1 & “') AND ((Customer.Customer_Location) ALike '” & sLocation1 & “'));

The problem I have is that not all the records are returned in the query. If I remove the reference in the Where to the customer table then I get the records.
Why must I have an inner join on the customer table?
I thought (apparently wrong) that I needed to change the joins to LEFT from Inner to fix this problem. The Left joins you see above were all inner previously.
Any help from you geniuses would be appreciated.

 

[lespaul]

read Understanding SQL Joins for how joins work and what records you get when using them.

I've also never seen ALike in a query; if you are trying to use LIKE you'll need to add the wildcard character '*' (JET SQL) or '%' (all other SQL)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design