CODEWITHSUNDEEP
sqlsql-server
Developer
Developer

Reputation: 428

student inside school query

Let's say I have a schools table and a students table. The students table is connected to the schools table and has the columns NAME and BIRTHDATE.

I need all the schools that have students inside them, who are called "jim" or "mike" and are born on the same day.

Something like : 

select *
  from schools s
  join student st in st.schoolId = s.Id
  where (...)

Upvotes: 2

Views: 131

Answers (6)

etsa
etsa

Reputation: 5060

Another form (I haven't tested it) could be:

SELECT * 
 FROM SCHOOLS S
 INNER JOIN (
             SELECT A.SCHOOL_ID 
             FROM STUDENT A
             INNER JOIN STUDENT B ON A.BIRTHDATE = B.BIRTHDATE  AND A.SCHOOL_ID= B.SCHOOL_ID
             WHERE A.NAME = 'JIM'
             AND B.NAME = 'MIKE'
            ) C ON S.ID = C.SCHOOL_ID

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39537

You can simply use conditional aggregation to find the schoolIds that have both the names per birthdate and join (or use IN) with schools table to get the relevant details.

select s.*
from schools s
join (
    select distinct schoodId
    from student
    where name in ('jim', 'mike')
    group by schoodId,
        birthdate
    having count(distinct name) = 2
    ) st on s.Id = st.schoodId;

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133410

Could be using tow time the tables related inner joi by birthdate 

select t1.id from ( 
    select s.id , st.name, st.birthdate
    from schools s
    join student st in st.schoolId = s.Id
    where st.name  = 'jim' 
) t1 
inner join ( 
    select s.id , st.name, st.birthdate
    from schools s
    join student st in st.schoolId = s.Id
    where st.name  = 'mike' 
) t2 on t1.birthdate = t2.birthdate

Upvotes: 1

M84
M84

Reputation: 745

Try with this:

select * from schools
where schools.id IN (

(select 
    s1.schoolID
from 
   student s1 inner join student s2
   on 
      (s1.schoolID = s2.schoolID) --seem school
      AND (s1.birthdate = s2.birthdate) --seem birthdate
      AND ((s1.name like '% jim %') OR (s1.name like '% mike %'))) --name jim or mike
)

hope this help!

Upvotes: 0

Stefano Zanini
Stefano Zanini

Reputation: 5926

You can do that by joining students with schools twice

select  distinct s.*
from    schools s
join    student st1
on      st.schoolId = s.Id
join    student st2
on      st.schoolId = s.Id
where   st1.birthdate = st2.birthdate and
        st1.name = 'Jim' and
        st2.name = 'Mike'

Upvotes: 2

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

You'd be looking for:

select s.*
from schools s
    cross apply (select birthdate, name from students st where st.schoolId = s.Id and st.name = 'Jim') studentJim
    cross apply (select birthdate, name from students st where st.schoolId = s.Id and st.name = 'Mike') studentMike
where studentJim.birthDate = studentMike.birthDate

Upvotes: 1

Related Questions