February 13th, 2003, 10:25 AM
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!
February 13th, 2003, 10:56 AM
The table layout sounds okay.
To get all CDs that have not been voted on
select * from cds
where cdid not in (select cdid from ratings)
February 13th, 2003, 11:06 AM
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!