Help finding a dummy row...

Question

Help finding a dummy row...

Ash-N

Say Hey Kid

Points: 703
More actions
March 14, 2007 at 11:54 pm

#177131

All,
I have a student class denormalized table where student and the courses he is registered to are being saved. There is a dummy cource where all the students
enrolled are initially assigned to. As students gets enrolled into different courses that records will get added in also. Again when a student registeres he
is enrolled into the dummy course.
eg
student  course
-----------------
tom      dummy
jerry     dummy
tom      math
tom      english
tom      biology
jerry     math
harry    dummy
Any help is greatly appreciated

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

What's your question?

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

Ash-N Say Hey Kid Points: 703 More actions · Answer 2

Hah... I forgot to post what the question was..

I need to write a query to find out the dummy rows if they exist in the table. (I donot know the value of the dummy row). ?

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

What distinguishes a dummy course from a real one?

I can give you a query that will find students with a single course, but if they have multiple, there will have to be a way to tell the dummy and real apart.

Unless you have a datestamp of some form there's no way to tell what row was inserted first.

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

Ash-N Say Hey Kid Points: 703 More actions · Answer 4

Thanks for the reply. The distinguishing feature of the dummy course is that all the students in the system will be assigned to that dummy row.

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

There's nothing else? Foreign key? Flag anywhere?

It's doable, but it's not going to be a nice query. Let me see what I can come up with....

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

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

Try this. It's not tested, but should work. It depends on the dummy course been the only one that all students take.

SELECT

* FROM courses WHERE course = (

SELECT course FROM courses GROUP BY courses

HAVING COUNT(*) = (SELECT COUNT(DISTINCT student) FROM courses)

)

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 7

Maybe this?

SELECT TOP 1 Course

FROM Courses

GROUP BY Course

ORDER BY COUNT(*) DESC

N 56°04'39.16"
E 12°55'05.25"

Ash-N Say Hey Kid Points: 703 More actions · Answer 8

Ash-N

Say Hey Kid

Points: 703

March 16, 2007 at 7:04 am

#695441

Thanks guys. Appreciate your help.