Subquery results set? - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Subquery results set?

Author

Topic

davidmal
Starting Member

19 Posts

Posted - 2007-01-20 : 12:54:43

I'm somewhat new to T-SQL and stuck on something probably simple.

I need to create a report listing students that have duplicate records. The ones with duplicates all have one invalid ID that starts with 9. (No valid IDs start with 9).
I need to have both records for the valid and invalid ID and omit the studets without duplicates.
Mock data
Table name Students
ID fname lname
======================================
123456 Bill Fold
900012 Bill Fold
321654 Sue Sample
852147 Stu Pedo
125477 Hugh Jorgan
900005 Anita Bath
152144 Anita Bath
900003 Denise Smith
115478 Denise Smith
102587 Gary Jones
124578 Miguel Perez

What statement will take the above and result this...
ID fname lname
======================================
123456 Bill Fold
900012 Bill Fold
900005 Anita Bath
152144 Anita Bath
900003 Denise Smith
115478 Denise Smith
I tried using subquerys like..
Select * from students
Where fname = (Select fname from students where id like '9%') and just get errors.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-20 : 17:12:08

Where fname in (Select fname from students where id like '9%')

or maybe

Where fname in (Select fname from students where convert(varchar(20),id) like '9%')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-20 : 18:38:55

select s1.id, s1.fname, s1.lanme
from students as s1
where exists (select null from students as s2 where s2.fname = s1.fname and s2.lname = s1.lname and convert(varchar(1), id) = '9'

Peter Larsson
Helsingborg, Sweden

davidmal
Starting Member

19 Posts

Posted - 2007-01-20 : 22:20:04

Thanks nr and peso. Problem resolved!!!

I first tried...
Select * from students
Where fname in (Select fname from students where id like '9%')

worked great on the mock data but when I applied it to the real data it included students with the first name of any student with an incorrect ID.

Then I added another condition...
Select * from students
Where (fname in (Select fname from students where id like '9%')
and
lname in (Select lname from students where id like '9%'))

I tested it and it works fine. Looks mighty sloppy but it works.

Thanks again,

David.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-21 : 04:26:29

No, it will not work 100%.

What happens if you have these four students?

123456 Bill Smith
852147 Andy Noname
900005 Andy Smith
152144 Andy Smith
900003 Bill Noname
115478 Bill Noname

You query above will fetch Bill Smith and Andy Noname as well,
because there is at least one other Bill with ID beginning with 9 AND one other Smith with ID beginning with 9.

You have to test for both firstname and lastname simultaneously, like my suggestion.

declare @students table (id int, fname varchar(10), lname varchar(10))

insert	@students
select	123456, 'Bill', 'Smith' union all
select	852147, 'Andy', 'Noname' union all
select	900005, 'Andy', 'Smith' union all
select	152144, 'Andy', 'Smith' union all
select	900003, 'Bill', 'Noname' union all
select	115478, 'Bill', 'Noname'

-- davidmal
Select	*
from	@students
Where	fname in (Select fname from @students where id like '9%')
	and
	lname in (Select lname from @students where id like '9%')

-- peso
select	s1.id,
	s1.fname,
	s1.lname
from	@students as s1
where	exists (select null from @students as s2 where s2.fname = s1.fname and s2.lname = s1.lname and id like '9%')

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources