CODEWITHSUNDEEP
mysqlselectunion
Paul M
Paul M

Reputation: 4157

Mysql union clause identify tables

I have a mysql table which holds lots of data across multiple tables. So to get it all out, I think the best bet is to use a UNION clause, however I also want to know which table the results come from as some have multiple values and some are null.

The following sql does not appear to work but I cant see whats wrong.

 (SELECT *, `2` AS 'cat' from `2` where `recid` = 'cneta0ld00ah')
  UNION
 (SELECT *, `3` AS 'cat' from `3` where `recid` = 'cneta0ld00ah')
  UNION
 (SELECT *, `4` AS 'cat' from `4` where `recid` = 'cneta0ld00ah')
  UNION
 (SELECT *, `5` AS 'cat' from `5` where `recid` = 'cneta0ld00ah')

All Tables look something like the following are are named, 1, 2, 3, 4 etc.

 recid         | item
 -------------------------
 cneta0ld00ah  |   1
 cneta0ld00ab  |   1
 cneta0ld00ad  |   3

I cant change the name of the tables as its an import from a very old database which we are trying to convert and extract data from.

If I run it without the 'table-name' AS 'cat', then it runs fine, as soon as I try to add in the table name it throws an error saying 

Unknown column '2' in 'field list'

It seems to be thinking 2 is a column name?

Upvotes: 0

Views: 159

Answers (3)

Ike Walker
Ike Walker

Reputation: 65547

The problem is the backticks you have around the 'table-name' AS 'cat'. Backticks are for quoting table and column names, not strings. You should use single quotes instead: 

 (SELECT *, '2' AS 'cat' from `2` where `recid` = 'cneta0ld00ah')
  UNION
 (SELECT *, '3' AS 'cat' from `3` where `recid` = 'cneta0ld00ah')
  UNION
 (SELECT *, '4' AS 'cat' from `4` where `recid` = 'cneta0ld00ah')
  UNION
 (SELECT *, '5' AS 'cat' from `5` where `recid` = 'cneta0ld00ah')

Upvotes: 3

Kermit
Kermit

Reputation: 34054

Use UNION ALL instead of UNION.

(SELECT *, `2` AS 'cat' from `2` where `recid` = 'cneta0ld00ah')
 UNION ALL
(SELECT *, `3` AS 'cat' from `3` where `recid` = 'cneta0ld00ah')
 UNION ALL
(SELECT *, `4` AS 'cat' from `4` where `recid` = 'cneta0ld00ah')
 UNION ALL
(SELECT *, `5` AS 'cat' from `5` where `recid` = 'cneta0ld00ah')

UNION should be used when you are selecting related information, similar to JOIN, since only distinct values will be selected. UNION ALL does not remove duplicates and therefore also decreases the time required to find distinct values.

You should also always specify a column list, and not SELECT *.

Upvotes: 0

podiluska
podiluska

Reputation: 51494

Do all the tables have the same structure? In a Union, with select *, they need to.

If so, try removing the brackets

SELECT recid, item, `2` AS `cat` from `2` where `recid` = 'cneta0ld00ah'
UNION 
SELECT recid, item, `3` AS `cat` from `3` where `recid` = 'cneta0ld00ah'

Upvotes: 0

Related Questions