2 Replies - 857 Views - Last Post: 29 April 2012 - 01:14 PM Rate Topic: -----

#1 AN1554  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 130
  • Joined: 03-April 11

Tell SQL to fetch records even if 2 fields are empty

Posted 29 April 2012 - 10:19 AM

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


Is This A Good Question/Topic? 0
  • +

Replies To: Tell SQL to fetch records even if 2 fields are empty

#2 kojima100  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 66
  • Joined: 10-November 10

Re: Tell SQL to fetch records even if 2 fields are empty

Posted 29 April 2012 - 11:37 AM

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))



Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5876
  • View blog
  • Posts: 12,757
  • Joined: 16-October 07

Re: Tell SQL to fetch records even if 2 fields are empty

Posted 29 April 2012 - 01:14 PM

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.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1