CODEWITHSUNDEEP
mysqlselect
James F
James F

Reputation: 554

Select all of the items that are owned by current user, show less items if they're not

Imagine you've got 3 tables: users, songs, and users_songs. Users are editors of their own songs or songs they've been invited to edit but they can't edit anyone elses.

users
-id
-username

songs
-id
-is_ready
-name

users_songs
-id
-user_id
-song_id

is_ready is either 0 or 1 - it means that the song is not ready/ready to be displayed in a listing on the front page.

I want to select ALL the songs that are owned by the current user id, whether ready=1 or ready=0, but only select the songs that are ready=1 if they're owned by other user ids.

I think I might be asking for conflicting joins so I'm not sure if it's possible..

Upvotes: 0

Views: 62

Answers (3)

roblovelock
roblovelock

Reputation: 1981

You can do this all in one query.

See example here

SELECT us.user_id, s.name, s.is_ready
FROM songs s, users u, users_songs us
WHERE us.song_id = s.id
and 
(u.id = us.user_id 
OR (us.user_id <> u.id AND s.is_ready = true))
AND u.id = 1;

Upvotes: 0

Alma Do
Alma Do

Reputation: 37365

It will be like ($user_id is current user id):

SELECT
  songs.*
FROM
  songs
    LEFT JOIN users_songs ON songs.id=users_songs.song_id
WHERE
  user_songs.user_id=$user_id
  OR
  is_ready=1

Upvotes: 1

Nathan H
Nathan H

Reputation: 49371

SELECT songs.*
FROM songs, users, users_songs
WHERE users.id = users_songs.user_id 
AND (users_songs.song_id = songs.id OR songs.is_ready = 1)
AND users.id = [current user id]

Try and let us know if that's what you meant.

Upvotes: 0

Related Questions