[jasper199069]

Hi All,

I have a question.
I want to filter artists on genre, this is a many-to-many combination.
This are the tables:
Artists Table
ID Title
1 Artist 1
2 Artist 2
3 Artist 3

Genres Table
ID Title
1 Rap
2 Rnb
3 Alternative
4 House

Artists_genres table
ID ArtistID GenresID
1 1 1
2 1 3
3 2 2
4 3 3
5 3 4

The goal is that when i filter on Rnb that i only see artist 2
When i filter on Rnb that i see artists 1 and 3 and when i filter on Rbn and Rap that i only see artist 1

The query i have at the moment works just for a bit when i select multiple genres it shows me the artists which have this genre.

This is my query

SELECT `tbl`.* FROM `artists` AS `tbl` INNER JOIN `artist_genres` ON (`tbl`.`artist_id` = `artist_genres`.`artist_id`) WHERE `artist_genres`.`genre_id` = '1' OR `artist_genres`.`genre_id` = '5'

I also tried queries like these

SELECT tbl.* FROM q206y_mmp_artists AS tbl WHERE EXISTS 
  (SELECT null FROM q206y_mmp_artist_genres AS at 
    WHERE at.mmp_artist_id = tbl.mmp_artist_id AND at.mmp_genre_id=1 AND at.mmp_genre_id = 3)

Any help is appreciated

Kind regards and Thanks in Advance.

Japie

This post has been edited by jasper199069: 27 September 2012 - 02:04 AM

[modi123_1]

Quote

The goal is that when i filter on Rnb that i only see artist 2
When i filter on Rnb that i see artists 1 and 3

What? That's filtering on the same thing and wanting two different results.

	SELECT `tbl`.* 
	FROM `artists` AS `tbl` 
	INNER JOIN `artist_genres` ON (`tbl`.`artist_id` = `artist_genres`.`artist_id`) 
	WHERE `artist_genres`.`genre_id` = '1' OR `artist_genres`.`genre_id` = '5'

What are you expecting here? you are filtering on rap and some other genre...


Joins are where you should be going.. exists are not really going be effective here.

[jasper199069]

Hi,

Thanks for your reply,
I am going to check if i can get it to work,

Thanks.

[jasper199069]

Hi modi123_1,

The joins worked,

Thanks for the info

Kind Regards,

Jasper