Greywacke Posted March 16, 2010 Share Posted March 16, 2010 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 Link to comment Share on other sites More sharing options...
Greywacke Posted March 16, 2010 Author Share Posted March 16, 2010 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). Link to comment Share on other sites More sharing options...
Greywacke Posted March 16, 2010 Author Share Posted March 16, 2010 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; Link to comment Share on other sites More sharing options...
Greywacke Posted March 16, 2010 Author Share Posted March 16, 2010 this issue has now been RESOLVED Link to comment Share on other sites More sharing options...
justsomeguy Posted March 16, 2010 Share Posted March 16, 2010 Happy to help. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.