Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2008
    Posts
    3

    Unanswered: Problem formulating a query

    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

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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???

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT statues.title
      FROM statues
    LEFT OUTER
      JOIN statuesavaliations
        ON statuesavaliations.statueid = statues.id
       AND statuesavaliations.userid = 'r937'
     WHERE statuesavaliations.userid IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2008
    Posts
    3
    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

  5. #5
    Join Date
    Jul 2008
    Posts
    3
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    is the result the same? yes

    do they perform the same? no

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •