Mismatch of columns in UNION ALL statement

Question

I have this query,

            (SELECT
            [NAME],
            [NAME] AS RESULT, 
            ' - ' AS VALUE, 
            0 AS ORDERING 
            FROM 
            [PARTY_MSTR] 
            UNION ALL 
            (SELECT 
            [PARTY_MSTR].[NAME], 
            '  - ' || [T1].[T_NAME], 
            (SELECT [T2].[T_NAME],
            SUM(COALESCE([T2].[VALUE1], 0)) +  FROM [T2] 
            CROSS JOIN [T1] ON [T1].[T_NAME] = [T2].[T_NAME] 
            GROUP BY [T2].[T_NAME]), 
            1 
            FROM 
            [PARTY_MSTR] 
            CROSS JOIN [T1] 
            ) AS X;

When I run this query, I got this error SELECTs to the left and right of UNION ALL do not have the same number of result columns

Now I remove the comments, and the second error comes, only a single result allowed for a SELECT that is part of an expression. What's wrong with this queries.? I am using SQLite database.

The columns returned by both the SELECT statements **must be the same**, Both in count and as their names. — Phantômaxx, Apr 25 '15 at 08:18
I wrote this query in C#. So I had to add them. Forgot to remove them. I think there is no big deal with it. — DhavalR, Apr 25 '15 at 16:12

score 0 · Answer 1 · answered Apr 25 '15 at 07:13

0

With the comment there are four fields on the left of the union and five on the right.

The subselect on the right of the union returns more than one row. That's why you get the second error.

... and please edit your code so the there is only SQL there.

answered Apr 25 '15 at 07:13

idstam

2,848
1
21
30

With the comments, the first error occurs. After removing comments, the second error occurs. – DhavalR Apr 25 '15 at 07:15
Exactly, that's why you got two answers. Remove the comment and fix your subselect. – idstam Apr 25 '15 at 07:16
If you remove GROUP BY the sub select will only return one row. That should make it run. – idstam Apr 25 '15 at 07:40

score 0 · Answer 2 · answered Apr 27 '15 at 03:50

Solved it.

        (SELECT
        [NAME],
        [NAME] AS RESULT, 
        ' - ' AS VALUE, 
        0 AS ORDERING 
        FROM 
        [PARTY_MSTR] 
        UNION ALL 
        SELECT 
        [PARTY_MSTR].[NAME], 
        '  - ' || [T1].[T_NAME], 
        (SELECT VALUE FROM ((SELECT [T2].[T_NAME],
        SUM(COALESCE([T2].[VALUE1], 0)) AS VALUE  FROM [T2] 
        CROSS JOIN [T1] ON [T1].[T_NAME] = [T2].[T_NAME] 
        GROUP BY [T2].[T_NAME]) AS Y)), 
        1 
        FROM 
        [PARTY_MSTR] 
        CROSS JOIN [T1] 
        ) AS X;

Mismatch of columns in UNION ALL statement

2 Answers2