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