Jump to content

4 table SELECT JOIN with possible null values


Greywacke
 Share

Recommended Posts

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 by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

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 by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

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

this issue has now been RESOLVED :)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...