sweebee sweebee - 1 month ago 9
MySQL Question

Mysql search multiple tables and limit to 1

I have 4 tables:

Artists:

id name
1 Band
2 DJ


Genres:

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


Categories:

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


Prices:

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


What I need is to use a keyword for searching in the name with a genre and category. And i need the lowest price.

So when I request this:

keyword: "band"
category: "1"
genre: "2"

the result should be:

Band 120


It should also ordered by name or by price.

I have this right now, but it returns multiple artists when they have multiple categories or genres.

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

Try to use 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
Comments