how to use multiple select statement in a query

Question

I found it difficult to join multiple select statement in a query. if i select a single query it works fine but joining the select statement with union command shows nothing. what am i doing that is likely wrong.

$sel=mysql_query ("SELECT  *
FROM studentmark  join
     student  
     ON studentmark.student_id = student.username join
     subject 
     ON subject.code = studentmark.code 
 where student.username='$name' AND studentmark.YEAR = '$ya' AND
    studentmark.TERM = 'THIRD') 
//it works fine without using the union for a single query but joining the query there is nothing display

UNION(SELECT TOTAL AS secondterm 
FROM studentmark
   JOIN subject ON subject.code=studentmark.code
WHERE studentmark.student_id='$name' 
 AND studentmark.YEAR='$ya' 
 AND studentmark.TERM = 'SECOND')UNION(SELECT TOTAL AS firstterm 
FROM studentmark
   JOIN subject ON subject.code=studentmark.code
WHERE studentmark.student_id='$name' 
 AND studentmark.YEAR='$ya' 
 AND studentmark.TERM = 'FIRST'");

 $fetch=mysql_fetch_array($sel);
 $count=mysql_num_rows($sel);

You are unioning a different number of columns: `Select * ` and `SELECT TOTAL AS secondterm `. This will not work. Possible solution: http://stackoverflow.com/questions/2309943/unioning-two-tables-with-different-number-of-columns — Ocaso Protal, Sep 04 '15 at 09:36
Your code is hard to read, please format a little bit better. It doesn't matter how much tables you use in your union, but the number of columns must match for every single query in the union: `(Select * from studentmark ...) UNION (Select TOTAL AS secondterm FROM studentmark ....) UNION (SELECT TOTAL AS firstterm FROM studentmark ...) ` The second and third query return only one column, but the first one returns a lot more columns. — Ocaso Protal, Sep 04 '15 at 10:53
Oh, and by the way: [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Ocaso Protal, Sep 04 '15 at 10:56
$sel=mysql_query ("SELECT test1,test2,test3,test4 FROM studentmark join student ON studentmark.student_id = student.username join subject ON subject.code = studentmark.code where student.username='$name' AND studentmark.YEAR = '$ya' AND studentmark.TERM = 'THIRD') — user5189527, Sep 04 '15 at 11:15
I am designing a student report card. I successfully created a first term 2nd term and 3rd term report card. My aim now is to have the first, second and third term added together to give a cumulative report card. Do i need to create a seperate table for these or i can still use the studentmark table. — user5189527, Sep 04 '15 at 12:27

score 0 · Answer 1 · answered Nov 16 '15 at 08:47

$sel=mysql_query ("SELECT  YEAR, TERM, CODE, student_id, ContAss20, AsgClassWk10, Test2nd10,Exam60, Total,tname
FROM studentmark  join
     student  
     ON studentmark.student_id = student.username join
     subject 
     ON subject.code = studentmark.code 
 where student.username='$name' AND studentmark.YEAR = '$ya' AND
    studentmark.TERM = 'THIRD') 
//it works fine without using the union for a single query but joining the query there is nothing display

UNION(SELECT null, null, null, null, null, null, null,null, Total as secondterm, null 
FROM studentmark
   JOIN subject ON subject.code=studentmark.code
WHERE studentmark.student_id='$name' 
 AND studentmark.YEAR='$ya' 
 AND studentmark.TERM = 'SECOND')UNION(SELECT TOTAL AS firstterm 
FROM studentmark
   JOIN subject ON subject.code=studentmark.code
WHERE studentmark.student_id='$name' 
 AND studentmark.YEAR='$ya' 
 AND studentmark.TERM = 'FIRST'");

 $fetch=mysql_fetch_array($sel);
 $count=mysql_num_rows($sel);

how to use multiple select statement in a query

1 Answers1