Cardinality violation: 1222 The used SELECT statements have a different number of columns

Question

Why am i gettin this error Cardinality violation: 1222 The used SELECT statements have a different number of columns

$query = "
SELECT DISTINCT s.studentid, s.fname, s.lname, s.studentcode, s.courseid, u.id 
FROM students as s,
courses as c, users as u, studentgrade as sg 
WHERE s.courseid = '$id' and u.id = (SELECT id FROM courses WHERE courseid = '$id') and u.id = (SELECT id FROM users WHERE username = '$nombre')
UNION ALL
SELECT SUM(sg.assignmentpoints) as sum
           FROM assignments as sg
           WHERE sg.courseid = 185
           GROUP BY sg.courseid
 ";

Well, it's perhaps time you learn what a UNION is before using it? Or at least now you're getting an error? - http://dev.mysql.com/doc/refman/5.0/en/union.html — hakre, Apr 27 '14 at 23:16
You can't use different fields in select clause when using `UNION` — Hardy, Apr 27 '14 at 23:16
Possible duplicate of: http://stackoverflow.com/q/2309943/367456 — hakre, Apr 27 '14 at 23:26
@CarlosPerez: The way you ask the question is not helpful. Can you at least explain why you thought that UNION is what you're looking for to solve the problem? Unless you share a minimum about what you tried and what you expected and what happened instead it's not possible to offer you any help. — hakre, Apr 27 '14 at 23:48
@hakre I don't if a UNION is what i need, but Im trygin to find a way to do it. What Im trying to do is to select name, last name ... and in the saem query select from a different table the sum of two assignment so I can assign a grade to a user — Carlos Perez, Apr 27 '14 at 23:57
So what is the relation between those two tables? the corseid or the userid? or both? First tell/see about the relations. — hakre, Apr 27 '14 at 23:59
Your just wrote: *so I can assign a grade to a user* - now you wrote there is no relation at all. If there is no relation between grade and user, I wonder how you want to manage it to assign a grade to a user. — hakre, Apr 28 '14 at 00:07
@hakre - I'm with you; I wouldn't want a grade belonging to a different person. Especially if I didn't take that class! This query also appears to be possibly open to [SQL Injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) - please learn how to mitigate this, or suffer the wrath of your end users. Or just, y'know, suddenly find everybody is passing every course... — Clockwork-Muse, Apr 28 '14 at 11:42

Alexey Palamar · Answer 1 · 2014-04-28T10:29:46.797

(
        SELECT DISTINCT s.studentid, s.fname, s.lname, s.studentcode, s.courseid, u.id 
        FROM students as s,
        courses as c, users as u, studentgrade as sg 
        WHERE s.courseid = '$id' and u.id = (SELECT id FROM courses WHERE courseid = '$id') and u.id = (SELECT id FROM users WHERE username = '$nombre')
)

- Query A.

(
SELECT SUM(sg.assignmentpoints) as sum
           FROM assignments as sg
           WHERE sg.courseid = 185
           GROUP BY sg.courseid
)

- Query B.

Query A should return the same number of columns as the request B. So In your case:

SELECT DISTINCT s.studentid, s.fname, s.lname, s.studentcode, s.courseid, u.id 
FROM students as s,
courses as c, users as u, studentgrade as sg 
WHERE s.courseid = '$id' and u.id = (SELECT id FROM courses WHERE courseid = '$id') and u.id = (SELECT id FROM users WHERE username = '$nombre')
UNION ALL
SELECT SUM(sg.assignmentpoints) as sum, '', '', '', '', '', '', '', '', ''
           FROM assignments as sg
           WHERE sg.courseid = 185
           GROUP BY sg.courseid

I think you need Join, not Union.

SELECT DISTINCT s.studentid, s.fname, s.lname, s.studentcode, s.courseid, u.id 
    FROM students as s,
    courses as c, users as u, studentgrade as sg 
    LEFT JOIN 
    (SELECT * 
    FROM assignments 
    GROUP BY courseid
    ) as asg on asg.courseid=s.courseid
    WHERE s.courseid = '$id' and u.id = (SELECT id FROM courses WHERE courseid = '$id') and u.id = (SELECT id FROM users WHERE username = '$nombre')

Now my number of columns match, but when I try to display the sum says that the sum is undefined — Carlos Perez, Apr 27 '14 at 23:23

Cardinality violation: 1222 The used SELECT statements have a different number of columns

1 Answers1