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 |
|
VeselaApo
Posting Yak Master
114 Posts |
 Posted - 2011-01-11 : 17:02:19
|
| Hi,I have 2 tables with enrolled students data. Both tables have a column that is Student ID, that identifies the students with a unique student numbers. The first table contains info only about the students who have complete registration. The second table contains the information of ALL students, including those who have completed the registration. I would like to extract from table 2 All students EXCEPT those who have completed the registration. How do I inter join the two tables to get this info? thanks! |
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2011-01-11 : 22:33:16
|
| Something LikeSelect T2.* from Table2 where not exists (Select 1 from Table1 T1 where T1.StudentID = T2.StudentID) |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-01-12 : 10:38:10
|
| you can also use NOT IN or LEFT JOIN to achieve result------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
VeselaApo
Posting Yak Master
114 Posts |
Posted - 2011-01-12 : 16:57:58
|
| it worked! thanks! now, another question related to this one. In the result table selection with student information, I have repeating student ID numbers if there are more than one record per student. I would like to count the unique student IDs that are contained in the table. what is the easiest way to do that? thx! |
|
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2011-01-12 : 22:30:33
|
| Try:Select count(distinct studentid) from Table2 T2 where not exists (Select 1 from Table1 T1 where T1.StudentID = T2.StudentID) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-01-13 : 10:51:16
|
| select studentid from Table2 group by studentid having count(*) = 1------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
retreiving data from 2 tables that is not overlapp