Mysql搜索多张表并限制为1张

Question

我有 4 个表:

艺术家:

id  name
1   Band
2   DJ

流派:

id  artist_id  genre_id
1   1          2
2   1          3
3   2          2

类别:

id  artist_id  category_id
1   1          1
2   1          2
3   2          3

价格:

id  artist_id  price  time
1   1          120    60
1   1          400    240

我需要的是使用关键字来搜索具有流派和类别的名称。我需要最低的价格。

所以当我提出这个要求时:

关键字:“乐队”类别:“1”类型:“2”

结果应该是:

Band  120

还应按名称或价格排序。

我现在有这个，但是当他们有多个类别或流派时它会返回多个艺术家。

SELECT * FROM artists
           LEFT JOIN artists_genres ON artists_genres.artist_id = artists.id 
           LEFT JOIN artists_categories ON artists_categories.artist_id = artists.id 
           LEFT JOIN artists_prices ON artists_prices.artist_id = artists.id 
           WHERE genre_id = $genre AND category_id = $cat AND name LIKE '%$keyword%'

Answer 1

尝试使用Group By:

SELECT * FROM artists
LEFT JOIN artists_genres ON artists_genres.artist_id = artists.id 
LEFT JOIN artists_categories ON artists_categories.artist_id = artists.id 
LEFT JOIN artists_prices ON artists_prices.artist_id = artists.id 
WHERE genre_id = $genre AND category_id = $cat AND name LIKE '%$keyword%'
GROUP BY artists.artists_id