4 table SELECT JOIN with possible null values

[Greywacke]

hi there,i have a SELECT JOIN statement and i need to select leads even if supplier id = 0, the region will be available. yet if i use LEFT JOIN i keep getting NULL as the joined values, no matter from which table. is there a way to circuimvent this?here is the code:

SELECT 	6_serviceleads.bigint_LeadID, 	6_serviceleads.text_Consumer, 	6_serviceleads.text_LeadSubject, 	6_serviceleads.text_LeadAttributes, 	6_serviceleads.text_LeadMessage, 	6_serviceleads.bigint_ServiceID, 	6_serviceleads.bigint_SupplierID, 	6_serviceleads.bigint_RegionID, 	6_serviceleads.tinyint_LeadSent, 	6_serviceleads.timestamp_LeadCreated, 	2_servicescatalogue.bigint_ServiceID, 	2_servicescatalogue.text_ServiceDescription, 	2_servicescatalogue.bigint_CostPerLead, 	5_suppliers.bigint_SupplierID, 	5_suppliers.text_SupplierName, 	5_suppliers.text_SupplierW3, 	5_suppliers.text_ContactFirstName, 	5_suppliers.text_ContactSurname, 	5_suppliers.text_ContactPosition, 	5_suppliers.`text_ContactE-mail`, 	5_suppliers.bigint_ContactTel, 	5_suppliers.bigint_CurrentBalance, 	1_regions.bigint_RegionID, 	1_regions.text_RegionDescription FROM 6_serviceleads LEFT JOIN (	2_servicescatalogue, 	5_suppliers, 	1_regions) ON (	2_servicescatalogue.bigint_ServiceID = 6_serviceleads.bigint_ServiceID AND 	5_suppliers.bigint_SupplierID = 6_serviceleads.bigint_SupplierID AND 	1_regions.bigint_RegionID = 6_serviceleads.bigint_RegionID) WHERE 	6_serviceleads.bigint_ServiceID = 1 ORDER BY 	6_serviceleads.timestamp_LeadCreated DESC

Edited March 16, 2010 by Pierre 'Greywacke' du Toit

[Greywacke]

if i try doing a normal select from four tables, the records with supplier id 0 get omitted, they only show if i do a LEFT JOIN - but all the other records then return null values despite there being records.is there any way to select data from 4 tables, if one of the tables does not have matching records in it? (null values or complete omittal of the supplier table).

Edited March 16, 2010 by Pierre 'Greywacke' du Toit

[Greywacke]

okay i seem to have discovered a solution,

SELECT 	6_serviceleads.bigint_LeadID, 	6_serviceleads.text_Consumer, 	6_serviceleads.text_LeadSubject, 	6_serviceleads.text_LeadAttributes, 	6_serviceleads.text_LeadMessage, 	6_serviceleads.bigint_ServiceID, 	6_serviceleads.bigint_SupplierID, 	6_serviceleads.bigint_RegionID, 	6_serviceleads.tinyint_LeadSent, 	6_serviceleads.timestamp_LeadCreated, 	2_servicescatalogue.bigint_ServiceID, 	2_servicescatalogue.text_ServiceDescription, 	2_servicescatalogue.bigint_CostPerLead, 	5_suppliers.bigint_SupplierID, 	5_suppliers.text_SupplierName, 	5_suppliers.text_SupplierW3, 	5_suppliers.text_ContactFirstName, 	5_suppliers.text_ContactSurname, 	5_suppliers.text_ContactPosition, 	5_suppliers.`text_ContactE-mail`, 	5_suppliers.bigint_ContactTel, 	5_suppliers.bigint_CurrentBalance, 	1_regions.bigint_RegionID, 	1_regions.text_RegionDescription FROM 6_serviceleads LEFT JOIN 2_servicescatalogue 	ON (2_servicescatalogue.bigint_ServiceID = 6_serviceleads.bigint_ServiceID) LEFT JOIN 5_suppliers 	ON (5_suppliers.bigint_SupplierID = 6_serviceleads.bigint_SupplierID) LEFT JOIN 1_regions 	ON (1_regions.bigint_RegionID = 6_serviceleads.bigint_RegionID) WHERE 6_serviceleads.bigint_ServiceID = 1 ORDER BY 6_serviceleads.timestamp_LeadCreated DESC;

[Greywacke]

this issue has now been RESOLVED

[justsomeguy]

Happy to help.