mysql先选择再自然连接,MySQL从内部联接中选择1行

I'm don't know the terms for this problem, the title might be a little bit misleading.

I'm trying to create a overview of a 1 to many table but only want to select 1 example of the relation table.

Car table

name

id

Car colours

car_id

color

A car can have many different colors. However I want to select just one color for the 'overview'. How can I do this? My tries result in a lot of results with multiple colors of the same car row.

Preferably in one query if possible.

Thanks in advance

edit:

I think my question is to vague. I'm trying to select all the cars within the car table but only 1 color (the first one it comes across).

Foo blue

Bar blue

Barfoo yellow

etc

However Foo has the colors, blue,yellow,red,black etc.

As for table create query, this is a 'dummy' version. I'm just trying to learn how to solve this problem with a push into the right direction.

解决方案

You need a query like this:

SELECT * FROM car

INNER JOIN car_color ON car.id = car_color.car_id

LIMIT 1 -- this will limit the results to only one row

edit: to get only one color for a car you can use group:

SELECT * FROM car

INNER JOIN car_color ON car.id = car_color.car_id

GROUP BY car.id -- this will group all the rows with the same car to only one row per car