Edit Question - KnowledgeHub

Title Body

Let's say we have two tables: 'Car' and 'Part', with a joining table in 'Car_Part'. Say I want to see all cars that have a part 123 in them. I could do this: SELECT Car.Col1, Car.Col2, Car.Col3 FROM Car INNER JOIN Car_Part ON Car_Part.Car_Id = Car.Car_Id WHERE Car_Part.Part_Id = @part_to_look_for GROUP BY Car.Col1, Car.Col2, Car.Col3 Or I could do this SELECT Car.Col1, Car.Col2, Car.Col3 FROM Car WHERE Car.Car_Id IN (SELECT Car_Id FROM Car_Part WHERE Part_Id = @part_to_look_for) Now, everything in me wants to use the first method because I've been brought up by good parents who instilled in me a puritanical hatred of sub-queries and a love of set theory, but it has been suggested to me that doing that big GROUP BY is worse than a sub-query. I should point out that we're on SQL Server 2008. I should also say that in reality I want to select based the Part Id, Part Type and possibly other things too. So, the query I want to do actually looks like this: SELECT Car.Col1, Car.Col2, Car.Col3 FROM Car INNER JOIN Car_Part ON Car_Part.Car_Id = Car.Car_Id INNER JOIN Part ON Part.Part_Id = Car_Part.Part_Id WHERE (@part_Id IS NULL OR Car_Part.Part_Id = @part_Id) AND (@part_type IS NULL OR Part.Part_Type = @part_type) GROUP BY Car.Col1, Car.Col2, Car.Col3 Or... SELECT Car.Col1, Car.Col2, Car.Col3 FROM Car WHERE (@part_Id IS NULL OR Car.Car_Id IN ( SELECT Car_Id FROM Car_Part WHERE Part_Id = @part_Id)) AND (@part_type IS NULL OR Car.Car_Id IN ( SELECT Car_Id FROM Car_Part INNER JOIN Part ON Part.Part_Id = Car_Part.Part_Id WHERE Part.Part_Type = @part_type))

Tags (comma-separated) Cancel