#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    6
    Rep Power
    0

    table organization and sql query


    I tried several different queries but am not getting my tables to work so I am considering redesigning them. Basically, what I have is a db for rating CDs. Currently, it has two tables: cds and ratings.

    cds table has the primary key cdId, along with other stuff about the cd. ratings table has the userId, the cdId they voted on, and what they voted on it (number between 1-10).

    That said, I want to produce an SQL that will show the cdId (and related cd info) that have NOT been voted on. Thus, it should look into the ratings table and leave the ones that have been voted on and show everything else from cds table. That way, when a cd has been voted on, it wont' show up next time.

    Is there a better approach than this on creating the table? And if not, how should I do the sql? This SQL function is being embedded into an ASP file so I'm getting stuck on both the SQL and ASP parts. Thanks!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,382
    Rep Power
    391
    The table layout sounds okay.

    To get all CDs that have not been voted on

    Code:
    select * from cds
     where cdid not in (select cdid from ratings)
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    6
    Rep Power
    0
    thank you so much! it works beautifully and you made it look so simple! for the life of me, i kept trying INNER JOIN, LEFT JOIN, and completely overlooked NOT IN. again, thanks!

IMN logo majestic logo threadwatch logo seochat tools logo