Problem formulating a query

sinosoidal · #1 (**permalink**) 07-02-08, 12:05

Hi,

I use MySQL and i would like to write a query that could extract the information of the following scenario.

I have a table which stores statues, there are people which are going to avaliate those statues (each one can avaliate more that one statue) so i created two other tables STATUESAVALIATIONS and AVALIATIONS

where each table has the following structure:

STATUES
ID | TITLE | ...

STATUESAVALIATIONS

ID | STATUEID | USERID | AVALIATIONID

AVALIATIONS

ID | ...

Well basically i want to obtain all the statues that weren't avaliated by a determined user.

If the user has never avaliated a statue, it must return all the record, if it has already avaliated one statue, that one will not be listed, and so on.

The statues avaliated by one user must be listed to user that havent avaliated that statue.

I have a query that does something but its not perfect yet:

SELECT * FROM STATUES WHERE STATUES.ID NOT IN (SELECT STATUEID FROM STATUESAVALIATIONS)

The problem is that this way I can't differentiate users. If i try to complement the query doing SELECT STATUEID, USERID FROM STATUESAVALIATIONS mysql gives error saying i can only select one column.

Any tips?

Thank you very much,

Best regards,

Nuno Santos

mike_bike_kite · #2 (**permalink**) 07-02-08, 12:21

Quote:

Originally Posted by sinosoidal

Well basically i want to obtain all the statues that weren't avaliated by a determined user.

SELECT * FROM STATUES WHERE STATUES.ID NOT IN (SELECT STATUEID FROM STATUESAVALIATIONS)

Change code to following (I wasn't sure how you identify users so I just used 123 as an id) :
Code:
SELECT * FROM STATUES WHERE STATUES.ID NOT IN (SELECT STATUEID FROM STATUESAVALIATIONS where USERID=123)
What does avaliate mean???

r937 · #3 (**permalink**) 07-02-08, 12:24

Code:

SELECT statues.title
  FROM statues
LEFT OUTER
  JOIN statuesavaliations
    ON statuesavaliations.statueid = statues.id
   AND statuesavaliations.userid = 'r937'
 WHERE statuesavaliations.userid IS NULL

sinosoidal · #4 (**permalink**) 07-02-08, 13:03

Quote:

Originally Posted by mike_bike_kite

Change code to following (I wasn't sure how you identify users so I just used 123 as an id) :
Code:
SELECT * FROM STATUES WHERE STATUES.ID NOT IN (SELECT STATUEID FROM STATUESAVALIATIONS where USERID=123)
What does avaliate mean???

oh, thats why there was no suggestion available. Avaliated was supposed to mean, object of avaliation. How can I say that?

Thx,

Nuno

sinosoidal · #5 (**permalink**) 07-02-08, 13:05

Quote:

Originally Posted by r937

Code:

SELECT statues.title
  FROM statues
LEFT OUTER
  JOIN statuesavaliations
    ON statuesavaliations.statueid = statues.id
   AND statuesavaliations.userid = 'r937'
 WHERE statuesavaliations.userid IS NULL

thx for the reply.

I have only realized that mike had already written a reply before seeing your. Altought it works i don't know what it means.

Is the result of mike's suggestion the same as yours?

Thx,

Nuno

r937 · #6 (**permalink**) 07-02-08, 13:13

is the result the same? yes

do they perform the same? no

mike_bike_kite · #7 (**permalink**) 07-02-08, 13:53

Quote:

Originally Posted by sinosoidal

Avaliated was supposed to mean, object of avaliation. How can I say that?

It's not in my dictionary therefore you can define it how you please but please make it believable.

Quote:

Originally Posted by r937

is the result the same? yes

do they perform the same? no

Rudy's respone will definitely be faster assuming you have enough data to be able to spot the difference. I just altered your code to make it work.

MBK

Internet Services

Web Development

Digital Marketing

Consumer Tech