Select Top 50 from left table using Left Join-CodePudding

SELECT  TOP(50)  *  FROM [Cars]
LEFT JOIN [Colors] ON [Cars].[ModelId] = [Colors].[ModelId]

Returns 50 rows but i want 50 cars, because there are multiple colors per car Top(50) is not working.

CodePudding user response：

As I can see, you are not using any information from Colors table. In this case you need not join with Colors table.

SELECT  TOP(50)  *  FROM [Cars]

If you are looking to get each color of a car then probably you should use a list aggregate function for colors along with cars. Assuming color is the column name in Colors

SELECT  TOP(50)  *,
                 STRING_AGG([Colors].color, ',') WITHIN GROUP (ORDER BY color) AS colors
FROM [Cars]
LEFT JOIN [Colors] ON [Cars].[ModelId] = [Colors].[ModelId]

CodePudding user response：

SELECT * FROM (Select TOP 50 * FROM Cars) cars
LEFT JOIN [Colors] colors ON cars.[ModelId] = colors.[ModelId]

Found the answer, this works.