|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
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!
|
|
#2
February 13th, 2003, 10:56 AM
|
|||
|
|||
|
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)
|
|
#3
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!
|
|
| Viewing: Dev Shed Forums > Databases > Database Management > table organization and sql query |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
