Need Help in SQL Query

[calvin]

Hi,

I want to get all the student from a Database table which store student certificate. For example, I need a query of student who "HAVE" 3 certificate(Cert A, B, C).

Certificate Table:

StudentName CertificateName
John Cert A
Wilson Cert B
John Cert B
John Cert C
Michael Cert A

Output:
John

sqlQuery = "Select * from CertificateTable Where (CertificateName = Cert A) AND (CertificateName = Cert B) AND (CertificateName= Cert C)"

This is my query, but it not works.

Calvin

 

[penfold69]

Something along the lines of:

Select StudentName,
Count(CertificateName)
from certificatetable
GROUP BY StudentName Having Count(CertificateName) = 3

(not sure if the Having clause is portable away from MySql)

completely untested, and off the top of my head.

B.

 

[Joe Mamma]

select s1.* 
[indent]from student s1 


[/indent]inner join



([indent]select name, count(*) cnt [indent]from (select distinct name, cert from student) temp

where cert in (A, B, C) 
group by name 
having count(*) = 3

[/indent][/indent]) [indent]s2 on s1.name = s2.name

[/indent]

 

[calvin]

Thanks to penfold69 and Joe Mamma, the query works

Calvin