refering to other tables in the where clause

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

refering to other tables in the where clause

Author

Topic

Stefan
Starting Member

11 Posts

Posted - 2006-04-03 : 10:36:30

hey

I have a table: signups where all people that signed up for a match are saved.
One of the fields in that table is "for_match" which holds the match id of the match the user signed up for.

Now i want to retrieve the names of the people that signed up.:

 
SELECT by_member 
FROM   signups

However this would return all names i want only the names of the people who signed up to a match that is not yet played. The "match" table has a field called: finished. which tells whether a match has been played or not. A 0= not played/finished yet 1= played/finished.

Shouldn't be that hard, but i'm still trying to get the hang of SQL

Hope you help me, thanks in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-03 : 10:47:20

Is this?

SELECT by_member
FROM signups
where finished=0

Madhivanan

Failing to plan is Planning to fail

Stefan
Starting Member

11 Posts

Posted - 2006-04-03 : 10:50:28

thanks for the reply,

Nah, because the 'finished' field is not in the signups table. Its in the match table.
And i dont think this would work:

SELECT by_member
FROM signups
where match.finished=0

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2006-04-03 : 10:56:30

[code]
SELECT by_member
FROM signups
where for_match in
(select match_id
from match
where match.finished=0)
[/code]

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-03 : 11:04:26

Somthing like these??

Select By_Member From SignUps Left outer join Match on
Signups.Match_Id = match.match_id and match.finished =0

or
let us konw wht is the linking field between signup table and match table?

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Stefan
Starting Member

11 Posts

Posted - 2006-04-03 : 11:06:31

Ahh, yes i think lars code is what i was looking for :)

haven't tried it yet but i'm pretty sure that will work.

thanks guys :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-03 : 11:09:01

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources