Return Students that have taken 1010 AND 1020

[uncgis]

I need to create a SQL Statement that will display all students that have taken Eng 1010 AND 1020...I used crse_code In ("1010","1020")...but the works more like an OR than an AND. It returns all rows of students that have only taken one of the classes...for instance just 1010 and not 1020. Does anyone now of a way around this?

Thanks,

 

[mrdenny]

Yes, you need to do a self join.

select a.*
from table a
join table b on a.id = b.id
   and b.crse_code = 1020
where a.crse_code = 1010

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[amrita418]

Try either of these:

select     a.id 
from       tableA a
where      a.course = 1010
           and exists (select    'x' 
                       from      tableB b 
                       where     a.id = b.id
                                 and b.id = 1020)

If a given student can take a course max one time then using below is faster:

select     id
from       tableA
where      course in (1010, 1020)
group by   id
having     count(*) > 1

Regards,
AA

 

[SQLBill]

Maybe I'm missing something here, why won't a regular AND work?

SELECT <columns>
FROM <tablename>
WHERE Crse_Code = 1010
  AND Crse_Code = 1020

-SQLBill

Posting advice: FAQ481-4875

 

[gmmastros]

Return Students that have taken 1010 AND 1020

The OP is looking for students that have 2 records in the table, where 1 record has course code=1010 and another record with course code=1020.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[uncgis]

Thanks for everyones respones...I have posted the SQL below that I am going to use...in case anyone is interested. Amrita418's having code worked out also...but some people had taken the same english class twice...


Select distinct Shrtckn_Pidm From Shrtckn
Where shrtckn_pidm In (Select shrtckn_pidm
From shrtckn
Where shrtckn_crse_numb in ('1010', '1017','101')
and shrtckn_subj_code in ('ENGL','EH'))
And shrtckn_pidm In (Select shrtckn_pidm
From shrtckn
Where shrtckn_crse_numb IN ('1020', '1027','102')
and shrtckn_subj_code in ('ENGL','EH'))

Thanks again everyone!

 

[zhavic]

I thing Denny's solution should work too and it may be faster

select a.Shrtckn_Pidm
from Shrtckn a
join Shrtckn b on a.Shrtckn_Pidm = b.Shrtckn_Pidm
	and b.shrtckn_subj_code in ('ENGL','EH')
   and b.shrtckn_crse_numb in ('1010', '1017','101')
where a.shrtckn_subj_code in ('ENGL','EH')
	and a.shrtckn_crse_numb IN ('1020', '1027','102')
group by a.Shrtckn_Pidm

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.