#1222 - the Used Select Statements Have a Different Number of Columns

#1222 - The used SELECT statements have a different number of columns

The first statement in the UNION returns four columns:

SELECT b.id AS id, 
       b.pid AS pid, 
       b.message AS message, 
       b.date AS date 
  FROM wall_posts AS b 

The second one returns six, because the * expands to include all the columns from WALL_POSTS:

SELECT b.id, 
       b.date, 
       b.privacy,
       b.pid. 
       b.uid message
  FROM wall_posts AS b 

The UNION and UNION ALL operators require that:

1. The same number of columns exist in all the statements that make up the UNION'd query
2. The data types have to match at each position/column

Use:

FROM ((SELECT b.id AS id, 
             b.pid AS pid, 
             b.message AS message, 
             b.date AS date 
        FROM wall_posts AS b 
        JOIN Friends AS f ON f.id = b.pid 
       WHERE f.buddy_id = '1' AND f.status = 'b'
    ORDER BY date DESC
       LIMIT 0, 10)
      UNION
      (SELECT id,
              pid,
              message,
              date
         FROM wall_posts
        WHERE pid = '1'
     ORDER BY date DESC
        LIMIT 0, 10))

Error Code: 1222. The used SELECT statements have a different number of columns

The SELECT statement before the UNION keyword selects 4 columns but the SELECT statement after the UNION keyword selects only 1 column. You cannot build a UNION with different number of columns, thats the reason you get this error message about "different number of columns".

error when I call procedure in mysql:Error Code: 1222. The used SELECT statements have a different number of columns

The error message is a little obtuse.

The used SELECT statements have a different number of columns

It means this query

SELECT numpieza, count(numvend) into nvend from preci...

doesn't work because the SELECT mentions two columns. But you're using into to tell MySQL where to put the value of only one of the columns. It doesn't know what to do with the other column. Try this instead.

SELECT count(numvend) into nvend from preci...

Pro tip Stored procedures are tricky to debug. It's helpful to try the queries in them before you wrap them in the procedure.

Error: The used SELECT statements have a different number of columns

UNIONs (UNION and UNION ALL) require that all the queries being UNION'd have:

1. The same number of columns in the SELECT clause
2. The column data type has to match at each position

Your query has:

SELECT f.*, u1.*, u2.* ...
UNION 
SELECT fid2 FROM friends

The easiest re-write I have is:

   SELECT f.*, u.*
     FROM FRIENDS AS f
     JOIN USERS AS u ON u.uid = f.fid2
    WHERE f.fid1 = 1 
      AND f.fid2 > 1
UNION 
   SELECT f.*, u.*
     FROM FRIENDS AS f
     JOIN USERS AS u ON u.uid = f.fid1
    WHERE f.fid2  = 1 
      AND f.fid1 < 1
ORDER BY RAND()
LIMIT 6;

You've LEFT JOIN'd to the USERS table twice, but don't appear to be using the information.

MySQL Error: The used SELECT statements have a different number of columns

Since the number of columns has to match for a union statement you can add two dummy columns in the last statement and fix the order so it matches the other select statements in the union. Give this a try:

SELECT 
Client_Portfolio.idClient,
Client_Portfolio.idPortfolio,
Client.Name AS "Client_Name",
Provider.Name AS "Provider_Name",
"One" AS "Income_Type",
One.`One_Gross_Fee` AS "Gross_Fee",
One.`One_V_Fee` AS "V_Fee",
One.`One_E_Fee` AS "E_Fee",
One.`One_I_Fee` AS "I_Fee",
One.`One_Tax_Provision` AS "Tax_Provision",
One.`One_Net_Income` AS "Net_Income",
"N/A" AS "VAT",
One.`Updated_Date`
FROM Client_Portfolio
INNER JOIN Portfolio ON Portfolio.`idPortfolio` = Client_Portfolio.`idPortfolio`
INNER JOIN Client ON Client.idClient = Client_Portfolio.idClient
JOIN Provider ON Provider.idProvider = Portfolio.idProvider
INNER JOIN One ON One.idPortfolio = Portfolio.idPortfolio

UNION

SELECT 
Client_Portfolio.idClient,
Client_Portfolio.idPortfolio,
Client.Name AS "Client_Name",
Provider.Name AS "Provider_Name",
"Two" AS "Income_Type",
Two.`Two_Gross_Fee` AS "Gross_Fee",
Two.`Two_V_Fee` AS "V_Fee",
Two.`Two_E_Fee` AS "E_Fee",
Two.`Two_I_Fee` AS "I_Fee",
Two.`Two_Tax_Provision` AS "Tax_Provision",
Two.`Two_Net_Income` AS "Net_Income",
Two.`Two_Vat` AS "VAT",
Two.`Updated_Date`
FROM Client_Portfolio
INNER JOIN Portfolio ON Portfolio.`idPortfolio` = Client_Portfolio.`idPortfolio`
INNER JOIN Client ON Client.idClient = Client_Portfolio.idClient
JOIN Provider ON Provider.idProvider = Portfolio.idProvider
INNER JOIN Two ON Two.idPortfolio = Portfolio.idPortfolio

UNION

SELECT 
null AS idClient,
null AS idPortfolio,
Client.Name AS "Client_Name",
Provider.Name AS "Provider_Name",
"Three" AS "Income_Type",
Three.`Gross_Fee` AS "Gross_Fee",
0.0 AS "V_Fee",
Three.`Three_E_Fee` AS "E_Fee",
0.0 AS "I_Fee",
Three.`Three_Tax_Provision` AS "Tax_Provision",
Three.`Three_Net_Income` AS "Net_Income",
Three.`Three_Vat` AS "VAT",
Three.`Date` AS "Updated_Date"
FROM Three
INNER JOIN Three_Obs ON Three_Obs.`idThree_Obs` = Three.`idThree_Obs`
LEFT JOIN Client ON Three_Obs.`idClient` = Client.`idClient`
LEFT JOIN Provider ON Three_Obs.`idProvider` = Provider.`idProvider`

Although, looking at what your query seems to do I think you could rewrite it to use joins at least for the common tables client and provider and reduce the query a bit.

Error when using function: The used SELECT statements have a different number of columns

You are selecting two columns but inserting into only one variable. Presumably, you intend:

SELECT users.profile_verification
INTO isVerified

#1222 - The used SELECT statements have a different number of columns UNION error

You have different number of columns indeed. The last UNION number of column is only one column seleccted, where the first select you had selected 5 as following:

INSERT INTO email_queues (queueid, queuetype, ownerid, processed, recipient)
    SELECT DISTINCT 323,                -- These are five columns
                    'export',
                    1,
                    0,
                    subscriberid
    FROM
    (
       ....
    ) AS T1
    WHERE subscriberid NOT IN (... )
    UNION
    SELECT DISTINCT subscribers.subscriberid AS subscriberid -- This where is the error, only one column selected
    FROM email_list_subscribers AS subscribers
    JOIN email_lists AS lists ON lists.listid = subscribers.listid
    AND lists.listid IN (33)
    WHERE subscribers.listid IN (33)
      AND ((subscribers.emailaddress LIKE '%v%'))

You have to modify it like this in order to make it works:

INSERT INTO email_queues (queueid, queuetype, ownerid, processed, recipient)
SELECT DISTINCT 323,                -- These are five columns
                'export',
                1,
                0,
                subscriberid
FROM
(
   SELECT DISTINCT subscribers.subscriberid AS subscriberid
   FROM email_list_subscribers AS subscribers
   JOIN email_lists AS lists ON lists.listid = subscribers.listid AND lists.listid IN (34)
   WHERE subscribers.listid IN (34)
     AND ((subscribers.emailaddress LIKE '%a%'))
   UNION SELECT DISTINCT subscribers.subscriberid AS subscriberid
   FROM email_list_subscribers AS subscribers
   JOIN email_lists AS lists ON lists.listid = subscribers.listid AND lists.listid IN (37)
   WHERE subscribers.listid IN (37)
     AND ((subscribers.emailaddress LIKE '%kim%'))
) AS T1
WHERE subscriberid NOT IN (SELECT DISTINCT subscribers.subscriberid AS subscriberid
                           FROM email_list_subscribers AS subscribers
                           JOIN email_lists AS lists 
                             ON lists.listid = subscribers.listid  
                            AND lists.listid IN (37, 34)
                           WHERE subscribers.listid IN (37, 34)
                             AND ((subscribers.emailaddress LIKE '%a%'
                                  OR subscribers.emailaddress LIKE '%kim%'))
                          )
UNION
SELECT DISTINCT -- You have to add these in order to make the query works
  323,              
  'export',
  1,
  0,
  subscribers.subscriberid AS subscriberid -- this is only one column you selected
FROM email_list_subscribers AS subscribers
JOIN email_lists AS lists ON lists.listid = subscribers.listid
AND lists.listid IN (33)
WHERE subscribers.listid IN (33)
  AND ((subscribers.emailaddress LIKE '%v%'))

---

UPDATE: You can also move that query inside the subquery, something like this

INSERT INTO email_queues (queueid, queuetype, ownerid, processed, recipient)
SELECT DISTINCT 323,                -- These are five columns
                'export',
                1,
                0,
                subscriberid
FROM
(
   SELECT DISTINCT subscribers.subscriberid AS subscriberid
   FROM email_list_subscribers AS subscribers
   JOIN email_lists AS lists ON lists.listid = subscribers.listid AND lists.listid IN (34)
   WHERE subscribers.listid IN (34)
     AND ((subscribers.emailaddress LIKE '%a%'))
   UNION SELECT DISTINCT subscribers.subscriberid AS subscriberid
   FROM email_list_subscribers AS subscribers
   JOIN email_lists AS lists ON lists.listid = subscribers.listid AND lists.listid IN (37)
   WHERE subscribers.listid IN (37)
     AND ((subscribers.emailaddress LIKE '%kim%'))

  UNION

  SELECT DISTINCT subscribers.subscriberid AS subscriberid -- this is only one column you selected
FROM email_list_subscribers AS subscribers
JOIN email_lists AS lists ON lists.listid = subscribers.listid
AND lists.listid IN (33)
WHERE subscribers.listid IN (33)
  AND ((subscribers.emailaddress LIKE '%v%')) 
) AS T1
WHERE subscriberid NOT IN (SELECT DISTINCT subscribers.subscriberid AS subscriberid
                           FROM email_list_subscribers AS subscribers
                           JOIN email_lists AS lists 
                             ON lists.listid = subscribers.listid  
                            AND lists.listid IN (37, 34)
                           WHERE subscribers.listid IN (37, 34)
                             AND ((subscribers.emailaddress LIKE '%a%'
                                  OR subscribers.emailaddress LIKE '%kim%'))
                          )

The used SELECT statements have a different number of columns

Put the columns names explicitly rather than *, and make sure the number of columns and data types match for the same column in each select.

Update:

I really don't think you want to be UNIONing those tables, based on the tables names. They don't seem to contain related data. If you post your schema and describe what you are trying to achieve it is likely we can provide better help.

the used select statements have a different number of columns mysql

I don't see why you are using a union (ie a vertical merge) when a join(a horizontal merge) would seem more appropriate

SELECT i.*,   
p.id AS 'PartId',
m.`Name` AS 'Model',
c.`Name` AS 'Color',
pt.`Name` AS 'PartType'
FROM
inventory i
JOIN part p ON p.Id = i.PartId
JOIN model m ON m.Id = p.ModelId
JOIN color c ON c.Id = p.ColorId
JOIN parttype pt ON pt.Id = p.PartTypeId 
WHERE
barcode1 = @Barcode
OR barcode2 = @Barcode
OR barcode3 = @Barcode
OR barcode4 = @Barcode

---

Related Topics