January 24, 2008 at 6:45 pm
I need to look through 2 tables, one which is Gradstudents has 157 rows(Lastname, Firstname, loginid, office are the COLUMNS) and the other which is TA (Teacher's Assistants) which has 20 rows (loginid, classnumber are the COLUMNS).
Some of the loginids in table 1 are in table 2, therefore there are TA's in the Gradstudents table.
The question asks for how many students are not TA's?
The answer is 140, but when i try to run the query below, i don't get a result.
Select distinct g.loginid, lastname, firstname
From gradstudents g
join ta t
on g.loginid = t.loginid
Where classnumber <> ('544','444','100','142','143','326','370','378','457','467','594')
What do i need to change in order to get 140 as a result?
Thanks again everyone.
Vinni
January 25, 2008 at 9:09 am
I think you need:
- left join
- change the <> to not in
- add or classnumber is null (because there will be no records for non TAs in the ta table)
Select distinct g.loginid, lastname, firstname
From #gradstudents g
left join #ta t
on g.loginid = t.loginid
Where classnumber not in ('544','444','100','142','143','326','370','378','457','467','594')
or classnumber is null
January 25, 2008 at 9:25 am
assuming Gradstudent.loginid is a unique key, and "The question asks for how many students are not TA's? "
select count(*) from gradstudents
where loginid not in (select loginid from ta)
this statement will produce a number as the result.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply