0

I currently have this sql data:

id  track_name  artist_name media   source  is_correct  is_found

    9   track1          1           *****   spotify false           true
    10  track2          2           *****   itunes  false           true
    11  track3          3           *****   spotify false           true
    12  track4          4               *****   itunes  false           true

I need a sql query which results every song witch both (itunes & spotify) media. Is thre any way i can do this?

  • how many source do you have? – Raphael Oct 24 '13 at 12:16
  • two, but i would really like to make it dynamic, so unlimited –  Oct 24 '13 at 12:17
  • select * from table_name where source IN ("spotify", "itunes") is my first guess from what you are asking, however the question is a little vague. What are you trying to accomplish? This may help us help you. – Sean Larkin Oct 24 '13 at 12:19
  • I thought about grouping the data, and use the having clause like group by source having count(*) = 2 – Raphael Oct 24 '13 at 12:21
  • I wish to compare the cover art, so I want to place both cover arts next to eachother so I can tell wich is the correct album art for my application –  Oct 24 '13 at 12:22
  • With SELECT * FROM songs WHERE source IN ('itunes','spotify'); I still get seperate rows. I would like to get both media in ONE row –  Oct 24 '13 at 12:24
  • check out this post, you are looking for a pivot table http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns – Raphael Oct 24 '13 at 12:27

2 Answers2

0

try this

select * from tablename where  media IN ('itunes','spotify')
naveen goyal
  • 4,571
  • 2
  • 16
  • 26
0

I have my doubts that the cover art images will be identical, but barring that you need to join to your own table.

How does a MYSQL Self-Join Work?

select track1.id, track2.id 
from tablename track1
join tablename track2
where track1.media = track2.media and track1.source <> track2.source;
Community
  • 1
  • 1
Thomas
  • 3,348
  • 4
  • 35
  • 49