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:
ID | TITLE | ...
ID | STATUEID | USERID | AVALIATIONID
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.