inner join with extra conditions

Question

I'm trying to setup a rating app for images. I want to display the images that haven't been rated by the current logged user.

This covers two tables, I've successfully joined the tables but the extra conditions:

1. must be an image
2. exclude any images that have already been rated by the current user are not working.

It's displaying the results as if the 2 AND conditions aren't there at all.

SELECT 
   ver_data_media.id, ver_data_media.value, ver_data_media.ref, 
   ver_data_media.type, ver_data_rating.a
FROM 
   ver_data_media
LEFT JOIN 
   ver_data_rating ON ver_data_media.ref = ver_data_rating.a
                   AND ver_data_media.type = 'image'
                   AND ver_data_rating.a != $current_user->ID

Any advice would be greatly appreciated, thanks Frank.

Answer 1

Can you try the following? Choose the records for current user, then show images where rating is null/empty... perhaps. It is better if you had showed us your table schema thouhg..for a more effective answer.

SELECT ver_data_media.id, ver_data_media.value, 
ver_data_media.ref, ver_data_media.type, ver_data_rating.a
FROM ver_data_media
LEFT JOIN ver_data_rating
ON ver_data_media.ref = ver_data_rating.a
WHERE ver_data_media.type = 'image'
AND ver_data_rating.a is null
AND id = $current_user -- shouldn't this be like userid = $current_user, 
                        -- where is this ID coming from? what is its relation to this table?

Otherwise the most possible issue would be the data you are validating aren't the same type, ver_data_rating.a != $current_user->ID on this line. I guess the best suggestion would be to cast one data type into both ver_Data_rating.a and $current_user.

For e.g. you may use either of the following to test out.

• CAST(ver_data_rating.a AS INT) != CAST($current_user as INT)

• CAST(ver_data_rating.a AS INT) <> = CAST($current_user as INT)

---

UPDATE: with Recommended Table Schema and sample query

It seems like, the way your query connects, the relationships really do have some issue. Please take a look at this sample and let us know your results. I have listed two ways to get the unrated media records from media table for a particular user/user online. A variable is used to store the user id.

SQLFIDDLE DEMONSTRATION

Sample data:

-- media table
ID  VALUE   REF     TYPE
1   300     15      image
2   100     25      art
3   500     35      cartoon
4   200     35      image
5   100     25      image
6   200     15      image
7   400     15      image
8   250     15      image
9   500     15      image
10  180     15      art

-- rating table
RID     MID     UID     SOMETHING
100     1       15      xyz
101     2       25      abc
102     5       25      efg
103     3       35      abc

Step by step Queries:

set @user_id:=15;

-- find all media records for @user, type image
select * from media
where ref = @user_id
and type = 'image'
;

-- find all rated reords for @user
select mid from rating
 where uid = @user_id
;

-- media records that needs to be rated by @user
-- using IN clause
select * from media
where id not in 
(select mid from rating
 where uid = @user_id)
and type = 'image'
and ref = @user_id
;

-- media records that needs to be rated by @user
-- using INNER JOIN
select m.*
from media m
inner join (
select mid from rating
 where uid = @user_id) as x
on m.id <> x.mid
and m.type = 'image'
and m.ref = @user_id
;

Results for each step by step query above:

ID  VALUE   REF     TYPE
1   300     15      image
6   200     15      image
7   400     15      image
8   250     15      image
9   500     15      image

rated record ids from rating table under user = 15, type = image

MID
1

using IN clause : unrated media records for user = 15, type = image`

ID  VALUE   REF     TYPE
6   200     15      image
7   400     15      image
8   250     15      image
9   500     15      image

using INNER JOIN : unrated media records for user = 15, type = image

ID  VALUE   REF     TYPE
6   200     15      image
7   400     15      image
8   250     15      image
9   500     15      image

Answer 2

left join is basically short for left outer join. You need to separate your additional filters outside of the JOIN conditions and into the WHERE clause. Your query is saying get me all records from ver_data_media. Along with that, give me ver_data_ratings that match my condition or no ver_data_ratings otherwise. Hard to translate to english :) I also think, the columns you're comparing aren't right or should be renamed.

Try this...

SELECT ver_data_media.id, ver_data_media.value, ver_data_media.ref, ver_data_media.type, ver_data_rating.a
FROM ver_data_media
LEFT OUTER JOIN ver_data_rating
ON ver_data_media.id= ver_data_rating.media_id
WHERE ver_data_media.type = 'image'
AND ver_data_rating.user_id <> $current_user->ID