UNION SQL Issue

Question

I recently noticed the UNION function and saw on W3Schools that you can unite two table, but I want to unite three... I have made up a piece of code, however it doesn't work because I get an error. Is it possible to unite three tables and how can I achieve this? Thanks!

ERROR:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\Legendary\new\search.php on line 54

CODE:

            // Fill up array with names
            $sql2 = mysql_query("SELECT * FROM members UNION ALL SELECT * FROM clans UNION ALL SELECT * FROM tournaments"); 
            while($row=mysql_fetch_array($sql2)) {
                $a[]=$row['name'];
                $b[]=$row['id'];
            }

I'm pretty sure you want to JOIN these tables instead of UNIONing them. Also, take anything you read on W3Schools with a large grain of salt: [http://w3fools.com/](http://w3fools.com/) — Joe Stefanelli, Mar 05 '12 at 16:18
Okay, here's what it says: The used SELECT statements have a different number of columns — ZyteX, Mar 05 '12 at 16:19
The W3Schools page says in it's 2nd paragraph: "Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order." This statement may be arguable regarding the datatypes, but you should follow it regardless. — Mike Ryan, Mar 05 '12 at 16:23

score 4 · Accepted Answer · edited Mar 06 '12 at 09:00

You need to be returning the same number of columns and data types from each table and in the same order (because mySQL does not support the SQL standard's UNION CORRESPONDING syntax) . If the tables do not match exactly, but do have some similar columns that you would like to UNION, then specify only those columns in the SELECT clause for each part of the UNION.

Even if the table schemas match, it is good practice to specify the column names; otherwise adding a column to one of the tables in the future would break the query.

For example, if all tables had a Name column, you could do:

select Name from members
UNION ALL
select Name from clans
UNION ALL
select Name from tournaments

To clarify: you *need* to have the same number of columns. You *should* have the same types. (The latter rule is flexible. The former is not.) — ruakh, Mar 05 '12 at 16:20

score 3 · Answer 2 · answered Mar 05 '12 at 16:29

3

UNION is a SQL command to STACK the results on top of other results, which is why you need the same number of columns. If you are looking to see which clans and tournaments certain members have participated in, then you need to JOIN them.

answered Mar 05 '12 at 16:29

jle

9,058
5
47
66

UNION SQL Issue

2 Answers2