0

Could u please help me on this my sql query,

select STUD_ID,unit_c 
from FCHE_grad 
where (unit_c in("C0001","C0002","ENG300","K0001","K0002")) 
order by STUD_ID

the above query returns student_id, who have 4 unit . I want to display the student id, who having all the above 5 units?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
pradeepa
  • 27
  • 1
  • 7
  • Give us table schema. – Manwal May 12 '14 at 06:33
  • 2
    possible duplicate of [MySQL Select ID's which occur on different rows with multiple specific values for a column](http://stackoverflow.com/questions/7407001/mysql-select-ids-which-occur-on-different-rows-with-multiple-specific-values-fo) – Bill Karwin May 12 '14 at 06:36

2 Answers2

1

Use a having cluase to check if the student is in all 5 units

select * from FCHE_grad
where stud_id in (select STUD_ID
from FCHE_grad 
where (unit_c in('C0001','C0002','ENG300','K0001','K0002')) 
group by STUD_ID
having count(stud_id)=5);

Fiddle

G one
  • 2,679
  • 2
  • 14
  • 18
  • Thanks for your quick reply. When i run your query it will return only one entry correct (01821426 ENG300). But i want to display as 5 entries like same studentid with above unit_c . 01821426 C0001 01821426 C0002 01821426 ENG300 01821426 k0001 01821426 k0002 – pradeepa May 12 '14 at 07:08
  • @pradeepa: No prob.. Do accept the answer if it helped :) – G one May 12 '14 at 12:26
  • Hi G one, I need one more help in the same query. The result can contain 5 modules but these four compulsory ('C0001','C0002'','K0001','K0002'). – pradeepa Sep 10 '14 at 07:19
0

you should ' ' for varchars in where clause

try this

SELECT STUD_ID,unit_c 
FROM FCHE_grad 
WHERE unit_c ='C0001' AND 'C0002' AND 'ENG300' AND 'K0001' AND 'K0002'
ORDER BY STUD_ID
ravikumar
  • 893
  • 1
  • 8
  • 12