3 Replies - 455 Views - Last Post: 28 September 2012 - 01:30 AM Rate Topic: -----

#1 jasper199069  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 33
  • Joined: 19-August 08

Mysql filter many-to-many table question

Posted 27 September 2012 - 02:01 AM

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


Is This A Good Question/Topic? 0
  • +

Replies To: Mysql filter many-to-many table question

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9570
  • View blog
  • Posts: 36,240
  • Joined: 12-June 08

Re: Mysql filter many-to-many table question

Posted 27 September 2012 - 07:10 AM

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.
Was This Post Helpful? 1
  • +
  • -

#3 jasper199069  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 33
  • Joined: 19-August 08

Re: Mysql filter many-to-many table question

Posted 28 September 2012 - 12:05 AM

Hi,

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

Thanks.
Was This Post Helpful? 0
  • +
  • -

#4 jasper199069  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 33
  • Joined: 19-August 08

Re: Mysql filter many-to-many table question

Posted 28 September 2012 - 01:30 AM

Hi modi123_1,

The joins worked,

Thanks for the info :)

Kind Regards,

Jasper
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1