How to combine multiple queries in one table

Question

I have the following table:

CREATE TABLE studenttest ( YEAR INT, DEPT VARCHAR(5), SEM INT, REGNO INT, NAME VARCHAR(20), ENGLISH VARCHAR(2), MATHS VARCHAR(2), PHYSICS VARCHAR(2), CHEMISTRY VARCHAR(2), EG VARCHAR(2), FOC VARCHAR(2), LAB1 VARCHAR(2), LAB2 VARCHAR(2), LAB3 VARCHAR(2) ) 

With the following data:

 INSERT INTO studenttest values (2010,'cse',3,1,'saravaanan','a','b','c','d','ra','f','g','h','i'), (2010,'cse',3,2,'raja','ra','b','c','d','e','f','g','h','i'), (2010,'cse',3,3,'selvam','a','b','c','d','e','f','g','h','i') 

I want to request this data in order to get a result set of all students who do not have a “ra” grade in any of the subjects.

Answer 1

 SELECT a.* FROM studenttest a LEFT JOIN ( SELECT regno, name FROM studenttest WHERE ENGLISH = 'ra' OR MATHS = 'ra' OR PHYSICS = 'ra' OR CHEMISTRY = 'ra' OR EG = 'ra' OR FOC = 'ra' OR LAB1 = 'ra' OR LAB2 = 'ra' OR LAB3 = 'ra' ) b ON a.regno = b.regno AND a.Name = b.Name -- this line is OPTIONAL WHERE b.regno IS NULL 

• SQLFiddle Demo Link

Answer 2

Just an alternative to @Mahmoud version with UNPIVOT and CTE , this can also be done using UNION ALL :

 SELECT Name FROM ( SELECT Name, ENGLISH grade, 'English' subject from studenttest union all SELECT Name, MATHS grade, 'MATHS' subject from studenttest union all SELECT Name, PHYSICS grade, 'PHYSICS' subject from studenttest union all SELECT Name, CHEMISTRY grade, 'CHEMISTRY' subject from studenttest union all SELECT Name, EG grade, 'EG' subject from studenttest union all SELECT Name, FOC grade, 'FOC' subject from studenttest union all SELECT Name, LAB1 grade, 'LAB1' subject from studenttest union all SELECT Name, LAB2 grade, 'LAB2' subject from studenttest union all SELECT Name, LAB3 grade, 'LAB3' subject from studenttest ) un where grade <> 'ra' group by name having count(grade) = 9 

UNPIVOT performs the same step as UNION ALL with less code. These queries work fine when you have data that is not normalized like your table.

If you need all the data (and not just the names), you can again join the table query above:

 select * from studenttest t inner join ( SELECT Name FROM ( SELECT Name, ENGLISH grade, 'English' subject from studenttest union all SELECT Name, MATHS grade, 'MATHS' subject from studenttest union all SELECT Name, PHYSICS grade, 'PHYSICS' subject from studenttest union all SELECT Name, CHEMISTRY grade, 'CHEMISTRY' subject from studenttest union all SELECT Name, EG grade, 'EG' subject from studenttest union all SELECT Name, FOC grade, 'FOC' subject from studenttest union all SELECT Name, LAB1 grade, 'LAB1' subject from studenttest union all SELECT Name, LAB2 grade, 'LAB2' subject from studenttest union all SELECT Name, LAB3 grade, 'LAB3' subject from studenttest ) un where grade <> 'ra' group by name having count(grade) = 9 ) t2 on t.name = t2.name 

See SQL Fiddle with Demo

Answer 3

You can do it:

 WITH AllSubjectsGrades AS ( SELECT Name, Grade FROM ( SELECT Name, ENGLISH, MATHS, PHYSICS, CHEMISTRY, EG, FOC, LAB1, LAB2, LAB3 FROM studenttest ) t UNPIVOT ( grade FOR Subject IN(ENGLISH, MATHS, PHYSICS, CHEMISTRY, EG, FOC, LAB1, LAB2, LAB3) ) u ) SELECT Name FROM AllSubjectsGrades WHERE GRADE <> 'ra' GROUP BY Name HAVING COUNT(GRADE) = 9; 

SQL Fiddle Demo

This will give you only the selvam student, since saravaanan has a "ra" in the EG and raja has a "ra" in English. But selvam never got ra in any subject. And here is what

 GROUP BY Name HAVING COUNT(GRADE) = 9; 

Do.

Answer 4

Do you mean this?

 SELECT * FROM studenttest WHERE ENGLISH <> 'ra' AND MATHS <> 'ra' AND PHYSICS <> 'ra' AND CHEMISTRY <> 'ra' AND EG <> 'ra' AND FOC <> 'ra' AND LAB1 <> 'ra' AND LAB2 <> 'ra' AND LAB3 <> 'ra' 

Answer 5

Another way:

 SELECT * FROM studenttest WHERE 'ra' NOT IN (ENGLISH, MATHS, PHYSICS, CHEMISTRY, EG, FOC, LAB1, LAB2, LAB3) ; 

Answer 6

 SELECT * FROM studenttest WHERE ENGLISH + ',' + MATHS + ',' + PHYSICS + ',' + CHEMISTRY + ',' + EG + ',' + FOC + ',' + LAB1 + ',' + LAB2 + ',' + LAB3 NOT LIKE '%ra%' 

SQLFiddle Demo