CODEWITHSUNDEEP
mysql
rubberchicken
rubberchicken

Reputation: 1322

mysql query 2 tables with different columns?

i'm trying to do 2 queries in 1 basically with 2 different tables with different columns.. i tried this: 

SELECT * FROM $TIMES_TABLE WHERE uid='$contextUser' AND submitstatus=1 UNION SELECT first_name, last_name, employid, manager FROM $USER_TABLE WHERE username='$contextUser'

but i get this error:

Can't perform query: The used SELECT statements have a different number of columns

i don't understand. can someone please help

thanks

Upvotes: 0

Views: 448

Answers (3)

spencer7593
spencer7593

Reputation: 108420

A query returns a resultset, which is "defined" by the number of columns in the resultset AND the datatypes of each column.

A UNION or UNION ALL operation is designed to "append" two resultsets together; and in order to do that the resultsets from the two queries must be identical in terms of the number of columns returned, and the datatypes of each column must also be the same.

Sometimes, when the resultsets are off by just a little bit, we can fudge the query a bit, to make the resultsets match (adding some literals as column placeholders)

SELECT 'u'                 AS source
     , u.first_name        AS first_name
     , u.last_name         AS last_name 
     , u.employid          AS employid
     , u.manager           AS manager 
  FROM $USER_TABLE u
 WHERE u.username='$contextUser'
 UNION ALL
SELECT 't'                 AS source
     , t.some_string       AS first_name
     , t.some_otherstring  AS last_name 
     , 0                   AS employid
     , 0                   AS manager
  FROM $TIMES_TABLE t 
 WHERE t.uid='$contextUser'
   AND t.submitstatus=1

Note, the names of columns (or aliases assigned to the columns or expressions) are not important; what's important here is that both queries return five columns, and the datatypes of each column position matches: VARCHAR, VARCHAR, VARCHAR, INT, INT

When developing a SQL statement, it's important to define what resultset is going to be returned. How many columns, and the datatype of each column.

When developing a query that uses a "UNION" or "UNION ALL" operation, the resultsets from each query has to match.

Upvotes: 0

Nelson Patricio Jimenez
Nelson Patricio Jimenez

Reputation: 593

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

Search in stackoverflow before asking.

refer: The used SELECT statements have a different number of columns (REDUX!!)

Upvotes: 2

Eric Petroelje
Eric Petroelje

Reputation: 60498

All the rows in a query result must have the same columns. Since these two queries do not have the same columns, they can't be combined using UNION like that.

You would need to do this as two separate queries.

Upvotes: 0

Related Questions