SQL query to display data from multiple tables

Question

I searched for it on SO, but I could not get the correct answer.

Student ------------------ rollno int PK name varchar(20) class varchar(20) 

Another table

 Marks ----------------- rollno FK sub1 int sub2 int sub3 int sub4 int sub5 int 

sub1 , sub2 etc. contain item tags. Now I need a query that will display information about a student who has> 35 points in more than two subjects?

Answer 1

 select rollno, case when sub1 < 35 then 0 else 1 end + case when sub2 < 35 then 0 else 1 end + case when sub3 < 35 then 0 else 1 end + case when sub4 < 35 then 0 else 1 end + case when sub5 < 35 then 0 else 1 end + end as [Count] from student,marks where count > 2 

Answer 2

You can use CASE WHEN and summarize fields with more than 35.

 SELECT s.rollno, s.name, s.class FROM Student s join Marks m on (s.rollno = m.rollno) where (CASE WHEN sub1>35 THEN 1 ELSE 0 END + CASE WHEN sub2>35 THEN 1 ELSE 0 END + CASE WHEN sub3>35 THEN 1 ELSE 0 END + CASE WHEN sub4>35 THEN 1 ELSE 0 END + CASE WHEN sub5>35 THEN 1 ELSE 0 END) > 2; 

You can check it here SQL Fiddle ..

Answer 3

 DECLARE @tempRollnoTable(rno int) INSERT INTO @tempRollnoTable SELECT rollno FROM marks WHERE sub1 > 35 UNION ALL SELECT rollno FROM marks WHERE sub2 > 35 UNION ALL SELECT rollno FROM marks WHERE sub3 > 35 UNION ALL SELECT rollno FROM marks WHERE sub4 > 35 UNION ALL SELECT rollno FROM marks WHERE sub5 > 35 SELECT * FROM student WHERE rollno IN ( SELECT rno FROM @tempRollnoTable GROUP BY rno HAVING COUNT(*) > 2 ) 

Answer 4

try it

  select student.* from student inner join marks on student.rollno= marks.rollno where marks.rollno in( Select a.* from ( Select RollNo from marks where sub1>35 Union ALL Select RollNo from marks where sub2>35 Union ALL Select RollNo from marks where sub3>35 Union ALL Select RollNo from marks where sub4>35 Union ALL Select RollNo from marks where sub5>35) a having(rollno)>1) 

Answer 5

Your Marks table can be normalized first!

 Marks ----------------- rollno int Subject_ID int --( FK ) Subject_Val int Subjects ----------------- ID int Subject_Name varcahar(20) 

after you make this small change, everything will become more clear.

Answer 6

I am also new to coding, but you can try something like this, but before making the question more clear

 SELECT student.* from student InnerJoin marks ON studnet.roll_no=marks.student) where (sub1>=35 && sub2>=35 and so on)