-1

Let's say i have 3 tables:

table:     SONGS
cell:      ID            primary key
cell:      TITLE
cell:      ADD_TIME

table:     CATEGORIES
cell:      ID            primary key
cell:      title

table:     SONGS_CATEGORIES
cell:      ID            primary key
cell:      SONG_ID
cell:      CATEGORY_ID

Then let's assume that I have 3 songs(with id's 0, 1, 2), and I have 5 categories(rock, pop, rap and so on) and respectively, their id's(0, 1, 2...). How my sql query should look like, if I want to be able to select more than one category, then I need to look into table SONGS_CATEGORIES and get all songs_id with category id I selected, and then i get songs from table SONGS with those id's?

Hope i explained what i need pretty well, English is not my native language.

Radu Dascălu
  • 318
  • 4
  • 15
  • 1
    Use join syntax, perhaps an `in` statement for "if I want to be able to select more than one category" This may help you understand joins: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ – xQbert Jan 02 '15 at 20:51
  • I tried one `JOIN` and got stuck because i just can't imagine in my head how this should look like. – Radu Dascălu Jan 02 '15 at 20:57
  • You should have a read of this [Question and Answer](http://stackoverflow.com/q/12475850/1450077) I posted a while back. It is a really long tutorial just for instances like this. – Fluffeh Jan 02 '15 at 21:17
  • Thanks, that looks like a bunch of work, I will definitely read that :) – Radu Dascălu Jan 02 '15 at 21:20

3 Answers3

1

If you want to select songs from categories 1 & 2 :

SELECT s.id song_id, s.title song_title, s.add_time song_add_time, c.id cat_id, c.title cat_title
FROM CATEGORIES c
INNER JOIN SONGS_CATEGORIES sg
ON c.id = sg.category_id
INNER JOIN SONGS s
ON sg.song_id = s.id
WHERE c.id = 1 OR c.id = 2
Corentin
  • 149
  • 9
1

I guess this is what you need

SELECT * -- select what ever columns you need to display
FROM   SONGS s
       JOIN SONGS_CATEGORIES sc
         ON s.ID = sc.SONG_ID
       JOIN CATEGORIES c
         ON c.ID = sc.CATEGORY_ID 
 where c.title in ('title1','title2')
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
1

If you already know the ID numbers of the categories that you're wanting songs from then the following should do the trick:

SELECT *
FROM SONGS s
     JOIN SONGS_CATEGORIES sc
       ON s.ID = sc.SONG_ID
WHERE sc.CATEGORY_ID IN (@id1, @id2, @id3...@idN);

If you only know the names of the categories you want the songs for then you can use this:

SELECT *
FROM SONGS s
     JOIN SONGS_CATEGORIES sc
       ON s.ID = sc.SONG_ID
     JOIN CATEGORIES c
       ON sc.CATEGORY_ID = c.ID
WHERE c.title IN (@catTitle1, @catTitle2, @catTitle3...@catTitleN);

In both of these examples you would replace the @ with the data you are providing and adjust the SELECT part to show only the columns you need.

Roger Worrell
  • 64
  • 5