[AN1554]

SELECT tblPart.PartNumber, tblBrand.Brand, tblType.Type, tblVehicleBrand.Brand, tblModel.Model
FROM tblPart, tblBrand, tblType, tblVehicleBrand, tblModel
WHERE tblPart.Brand = tblBrand.BrandID AND tblPart.Type = tblType.TypeID AND tblType.Type = "Something" AND tblPart.VehicleBrand = tblVehicleBrand.BrandID AND tblPart.Model = tblModel.ModelID

From tblPart, there are also VehicleBrand and Model fields that need to be fetched. This data is not present for all records. The SQL above therefore fetches only the records where VehicleBrand and Model are not empty. Is there any way to make the SQL statement fetch the records where VehicleBrand and Model are empty as well?

This post has been edited by AN1554: 29 April 2012 - 10:27 AM

[kojima100]

try this:

SELECT tblPart.PartNumber, tblBrand.Brand, tblType.Type, tblVehicleBrand.Brand, tblModel.Model
FROM tblPart, tblBrand, tblType, tblVehicleBrand, tblModel
WHERE tblPart.Brand = tblBrand.BrandID AND tblPart.Type = tblType.TypeID AND tblType.Type = "Something" AND ((tblPart.VehicleBrand = tblVehicleBrand.BrandID) OR (tblPart.VehicleBrand IS NULL)) AND ((tblPart.Model = tblModel.ModelID) OR (tblPart.Model IS NULL))

[baavgai]

Start out with standard join syntax:

SELECT p.PartNumber, b.Brand, t.Type, vb.Brand, m.Model
	FROM tblPart p
		INNER JOIN tblBrand b
			ON p.Brand = b.BrandID 
		INNER JOIN tblType t
			ON p.Type = t.TypeID 
				AND tblType.Type = 'Something' -- I know of now DB that will let you do double quotes...
		INNER JOIN tblVehicleBrand vb
			ON p.VehicleBrand = vb.BrandID 
		INNER JOIN tblModel m
			ON p.Model = m.ModelID

You're saying you need data, even without VehicleBrand and Model, you change the join type:

SELECT p.PartNumber, b.Brand, t.Type, vb.Brand, m.Model
	FROM tblPart p
		INNER JOIN tblBrand b
			ON p.Brand = b.BrandID 
		INNER JOIN tblType t
			ON p.Type = t.TypeID 
				AND tblType.Type = 'Something'
		LEFT OUTER JOIN tblVehicleBrand vb
			ON p.VehicleBrand = vb.BrandID 
		LEFT OUTER JOIN tblModel m
			ON p.Model = m.ModelID

Hope this helps.