CODEWITHSUNDEEP
mysqlcodeigniter
Mani
Mani

Reputation: 135

How to select a specific record from MySQL database

I have three tables asrecord of students:
record of students
record of courses:
record of courses
Map student and course:
Map student and course

I want to get records from table std_course where student are as 1,2,3 using query

select course_id from std_course where std_id in (1,2,3) group by course_id

It returns me course_id as 1 but here std_id=4 also exist against course_id=1 I need to select course_id where std_id are only 1,2,3

Upvotes: 1

Views: 69

Answers (3)

Mani
Mani

Reputation: 135

The query that work for me after the changings in query Answered by @ϻᴇᴛᴀʟ is: 

select * from ( select sum(case when std_id in (1,2,3) then 1 else -1 end) tot , course_id from std_course group by course_id) t1 where t1.tot = 3

Upvotes: 0

Jacky
Jacky

Reputation: 781

You can try this using join: 

select * from std_course left outer join course on course.id= std_course.course_id where std_id <=3

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

You can use sum() and select case, std_id above 3 will be 0

select * from (
    select sum(case when std_id in (1, 2, 3) then 1 else 0 end) tot
        , course_id
    from std_course
    group by course_id) t1 
where t1.tot <= 3

Upvotes: 2

Related Questions