grizzly grizzly - 6 months ago 13
MySQL Question

Mysql Complex Select Query in 5 table

I have 5 table:


  1. mp3s

  2. albums

  3. remixes

  4. users

  5. likes



likes table:

╔════════╦══════════╦═════════╦═════════════╦═════════════════════╗
║ id ║ user_id ║ type_id ║ target_id ║ like_date ║
╠════════╬══════════╬═════════╬═════════════╬═════════════════════╣
║ 1 ║ 1 ║ 1 ║ 1049 ║ 2016-05-23 19:50:41 ║
╠════════╬══════════╬═════════╬═════════════╬═════════════════════╣
║ 2 ║ 2 ║ 2 ║ 457 ║ 2016-01-09 19:50:42 ║
╠════════╬══════════╬═════════╬═════════════╬═════════════════════╣
║ 3 ║ 2 ║ 3 ║ 457 ║ 2016-01-09 19:50:42 ║
╠════════╬══════════╬═════════╬═════════════╬═════════════════════╣
║ 4 ║ 2 ║ 1 ║ 457 ║ 2016-01-09 19:50:42 ║
╠════════╬══════════╬═════════╬═════════════╬═════════════════════╣
║ 5 ║ 3 ║ 3 ║ 4955 ║ 2016-06-12 19:50:41 ║
╚════════╩══════════╩═════════╩═════════════╩═════════════════════╝


type_id
columns:


  • 1--> mp3s

  • 2--> albums

  • 3--> remixes



i need this query like:

select col1, col2, col3
from likes, mp3s, albums, remixes
where likes.user_id == 2

if (likes.type_id == 1)
select col1, col2, col3
from mp3s
where likes.target_id == mp3s.id

union

if (likes.type_id == 2)
select col1, col2, col3
from albums
where likes.target_id == albums.id

union

if (likes.type_id == 3)
select col1, col2, col3
from remixes
where likes.target_id == remixes.id

order by likes.like_date desc
limit 0,20


thanks.

Answer

You'll need to use unions and joins

This selects all of the valid mp3 rows.

select 
    col1, col2, col3
from 
    likes
inner join
    mp3s
on likes.target_id = mp3s.id
where likes.type_id = 1 -- type is mp3

Now, so I dont do all the work for you -- create two more queries to get just the remixes and just the albums -- and then join them, maybe with a union?