[SOLVED] Rating System SELECT query

[w84me]

Hi all,

 

I have a problem with a query from a rating system i am currently developing for my school's webpage.

 

I have 3 tables

 

user: with (user_id,name,age) fields

 

items: with (item_id,item_name,item_description) fields

 

ratings: with (rating_id,item_id,user_id,rating) fields

 

What i want is to select all the items that have not been rated from the user yet.

 

I've managed to select all the items that has been rated with this query

 

SELECT items.* FROM items INNER JOIN ratings ON items.item_id = ratings.item_id AND ratings.user_id = '$user_id'

[aschk]

Use the [ code ] tags for highlighting code in the future please.

I think you'll want something like this:

 

SELECT i.*
FROM items i
JOIN ratings r ON r.item_id = i.item_id AND r.user_id != '$user_id'
GROUP BY i.item_id
ORDER BY i.item_id

 

This will SELECT ALL the items that HAVE RATINGS but are not rated by the user_id you have chosen.

 

note: the ORDER BY in the above statement is superfluous...

[w84me]

Thank you for your answer and sorry for the code highlighting.

 

This code you've written does indeed what you say but i want it to SELECT ALL that HAVE NO RATINGS and that are NOT rated by my the user_id

 

so if i write

 

SELECT i.*
FROM items i
JOIN ratings r ON r.item_id != i.item_id AND r.user_id != 1
GROUP BY i.item_id
ORDER BY i.item_id

 

I changed that r.item_id != i.item_id and

if i write user_id =1 it works fine...but if i write user_id = 2 it doubles the results and if i use 3 it triples them and so on....

[w84me]

 

I really need help with that people....It drives me crazy...

[fenway]

Then use aschk's code, switch to a LEFT JOIN and add "i.item_id IS NULL" to the where clause.

[gluck]

SELECT i.*
FROM items i
left JOIN ratings r ON r.item_id = i.item_id 
where r.user_id!='$user_id'

[fenway]

Um, there's no IS NULL there.

[gluck]

Um, there's no IS NULL there.

 

You will never ger i.item_id null. This is a left join so all records with an item id in items table will show up irrespective of their matching records in ratings table and there where will filter out the records that don't belong to the userid

 

SELECT i.*
FROM items i
left JOIN ratings r ON i.item_id = r.item_id 
where r.user_id!='$user_id'

[w84me]

Thank you all for your help but there is still problem...

 

This one is not working. It doesn't fetch any result

 

SELECT i.*
FROM items i
left JOIN ratings r ON i.item_id = r.item_id 
where r.user_id != '$user_id'

 

But this one fetches all the items that the user has rated

 

SELECT i.*
FROM items i
left JOIN ratings r ON i.item_id = r.item_id 
where r.user_id = '$user_id'

 

My sanity is in danger..... :'(

[ergecs]

is user_id integer or varchar, if it is integer try to remove single quotes and try <> instead of !=

[aschk]

you have a contradiction in your request:

"SELECT ALL that HAVE NO RATINGS and that are NOT rated by my the user_id"

 

You want ALL the items that are NOT rated , and the ones that are NOT rated by user_id, well the 1st part of your clause negates the 2nd part, ALL items that aren't rated, are obviously not rated by your user_id as well, because NO user has rated them, lol, this isn't the same as what I first thought you wanted.

Hence your new query should be:

SELECT i.*
FROM items i
LEFT JOIN ratings r ON r.item_id = i.item_id 
WHERE r.user_id IS NULL
GROUP BY i.item_id

[w84me]

You want ALL the items that are NOT rated , and the ones that are NOT rated by user_id, well the 1st part of your clause negates the 2nd part, ALL items that aren't rated, are obviously not rated by your user_id as well, because NO user has rated them, lol, this isn't the same as what I first thought you wanted.

Hence your new query should be:

 

You are right 

 

Hence the problem  with that query is that works only for one user.

 

I am going to have many users with different ids.

[w84me]

YES!!!! AT LAST!!! ;D

 

This works beautifully!!

 

SELECT i.*
FROM items i
LEFT JOIN ratings r ON r.item_id = i.item_id 
AND r.user_id = '$userid'
WHERE r.user_id IS NULL
GROUP BY i.item_id

 

I've added the

AND r.user_id = '$userid'

.

 

Thank you all!!! You are the best!! I've posted to tens of other forums and nobody could help me with that!!

 

I am going to cry of happiness!!!! :'( :'(