Need one row per teacher no matter how many schools they teach at.

Question

Need one row per teacher no matter how many schools they teach at.

Opus

Default port

Points: 1419
More actions
February 24, 2012 at 10:29 am

#268943

I have a Teacher table that contains one row per teacher per school that they teach at. For an Active Directory export, I need only one row from the teacher table no matter how many schools at.
INSERT INTO #mytable
(LASTFIRST, SCHOOLID, TID)
SELECT 'Teacher1','114','5845' UNION ALL
SELECT 'Teacher2','119','5000' UNION ALL
SELECT 'Teacher2','230','5346' UNION ALL
SELECT 'Teacher3','330','5998' UNION ALL
SELECT 'Teacher4','390','5111' UNION ALL
SELECT 'Teacher5','110','5321' UNION ALL
SELECT 'Teacher5','210','5789' UNION ALL
SELECT 'Teacher5','376','5002' UNION ALL
The TID is unique to that teacher at that school.
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

SELECT DISTINCT LASTFIRST FROM #mytable

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Opus Default port Points: 1419 More actions · Answer 2

That's what I was thinking should work but it didn't. I just come to find out some of the teacher names were entered with spaces. That's why they showed up as duplicates using distinct.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Then you're going to need to fix the data first, SQL can't tell that 'John Smith' and 'John Smith' are the same person.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

Opus (2/24/2012)
That's what I was thinking should work but it didn't. I just come to find out some of the teacher names were entered with spaces. That's why they showed up as duplicates using distinct.

As per your post TID's are unique so do a select distinct TID then join the table to itself on TID and get the name of the teacher.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

PaulB-TheOneAndOnly (2/24/2012)
Opus (2/24/2012)
That's what I was thinking should work but it didn't. I just come to find out some of the teacher names were entered with spaces. That's why they showed up as duplicates using distinct.
As per your post TID's are unique so do a select distinct TID then join the table to itself on TID and get the name of the teacher.

They're not unique. They're unique for the combination of teacher and school (Teacher2 has TID of 5000 and 5346).

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

GilaMonster (2/24/2012)
PaulB-TheOneAndOnly (2/24/2012)
Opus (2/24/2012)
That's what I was thinking should work but it didn't. I just come to find out some of the teacher names were entered with spaces. That's why they showed up as duplicates using distinct.
As per your post TID's are unique so do a select distinct TID then join the table to itself on TID and get the name of the teacher.
They're not unique. They're unique for the combination of teacher and school (Teacher2 has TID of 5000 and 5346).

You are right... I read what I wanted to read - which is a very dangerous thing to do.

Thank you for keeping me honest Gail.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Recurs1on SSCommitted Points: 1974 More actions · Answer 7

If the only "unique" piece of information you have is Name, you are also running the very real risk of reporting too few teachers. This will happen if there are any teachers across all schools that share the same name. You will only get one "John Smith" record, even if there are three different teachers named "John Smith" working across different schools.

Opus Default port Points: 1419 More actions · Answer 8

Opus

Default port

Points: 1419

February 24, 2012 at 12:19 pm

#1451550

Thanks all!