Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
richardlaw
Yak Posting Veteran
68 Posts |
 Posted - 2008-12-24 : 07:27:44
|
| HiI’m hoping someone can help me out.It’s not often I need to dabble with SQL in my web applications, most of what I do is basic stuff, but I’d like to create a view that’s a little more difficult – which is why I need some help.I’m using 2 tables – one a list of gymnasts, the other is a list of gymnastID’s in a table which identifies who’s doing what competitions (tblCompetitors). The competitors table also has a competitionID, as there is more than one competition.What I’d like to do is compare the two tables and display a list of gymnasts who’s NOT yet been assigned to a pre-defined competition. In other words:Select * from tblGymnasts who’s NOT listed in tblCompetitions Where the competitionID (in tblCompetitors) is ‘3’.Can someone please help me convert this into a real select statement?Many thanksRichard |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2008-12-24 : 07:36:55
|
| try this onceselect * from tblGymnasts g left join tblCompetitions c on g.gymnastID = c.gymnastID AND competitionID = 3 WHERE c.gymnastid IS NULL |
 |
|
|
Jai Krishna
Constraint Violating Yak Guru
333 Posts |
Posted - 2008-12-24 : 22:55:58
|
| SELECT * FROM tblGymnasts tg WHERE NOT EXISTS(SELECT * FROM tblcompetitions tc WHERE tg.gymnastid = tc.gymnastid AND Competitionid = 3)Jai Krishna |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-25 : 02:07:33
|
quote:
Originally posted by Jai Krishna
SELECT * FROM tblGymnasts tg WHERE NOT EXISTS(SELECT * FROM tblcompetitions tc WHERE tg.gymnastid = tc.gymnastid AND Competitionid = 3)Jai Krishna
no need to use * in subquery,select 1 will do |
|
|
|
|
|
|
Compare two tables