Problem in Join Queries

• mahwishali

  SSC Rookie

  Points: 32

  More actions

  September 25, 2008 at 6:05 am

  #382826

  I have 2 tables, Vehicle ( VehicleID, VehicleInfo) and Option (OptionID, OptionName) Having a many to many relationship via the table VehicleOpton (VehicleOptionID, VehicleID, OptionID)

  Option

  1 2Door

  2 4Door

  3 6Door

  4 8Door

  Vehicle

  1 Toyota

  2 Ford

  3 Sedan

  VehicleOption(VehicleOptionID,VehicleID,OptionID)

  1 1 3

  2 1 4

  3 2 4

  Now for any vehcleID provided in the Where Clause, i want to return a row (OptionID, OptionName, VehicleOptionID,VehicleID). It should return all options with VehicleOptionID = null if there is not record present in the VehicleOption Table corresponding to that vehicle.

  For example if vehicleID = 1, the query should return

  OptionID, OptionName, VehicleOptionID, VehicleID

  1 2Door null null

  2 4Door null null

  3 6Door 1 1

  4 8Door 2 1

  and if VehicleID = 3, it should return

  1 2Door null nulll

  2 4Door null nulll

  3 6Door null nulll

  4 8Door null nulll

  Any help would be highly appreciated. Thanx

• HanShi

  SSC-Dedicated

  Points: 33508

  More actions

  September 25, 2008 at 6:37 am

  #876935

  try this:

  SELECT *

  FROM [Option]

  LEFT OUTER JOIN

  (SELECT VehicleOption.OptionID, VehicleOption.VehicleOptionID, VehicleOption.VehicleID, VehicleInfo

  FROM VehicleOption

  LEFT OUTER JOIN Vehicle ON VehicleOption.VehicleID = Vehicle.VehicelID

  WHERE VehicleInfo = 'TOYOTA' -- change to you needs

  ) X

  ON [Option].OptionID = X.OptionID

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

• mahwishali

  SSC Rookie

  Points: 32

  More actions

  September 25, 2008 at 8:40 am

  #877057

  It Workedd... 🙂

  Thanx a lot...