mysql union question

Question

mysql> select job_desc_title from postings where id=194582;

+-----------------------------+
| job_desc_title              |
+-----------------------------+
| Speech/Language Pathologist |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select email_address,first_name,last_name,home_phone_area,home_phone_num from accounts
->  left join profiles on profiles.account_id=accounts.id  
->  where accounts.id=5;

+--------------------+------------+-----------+-----------------+----------------+
| email_address      | first_name | last_name | home_phone_area | home_phone_num |
+--------------------+------------+-----------+-----------------+----------------+
| [email protected] | Jianhua    | He        | 425             | 3584396        |
+--------------------+------------+-----------+-----------------+----------------+
1 row in set (0.00 sec)

I need to union the above 2 queries,but my trial failed:

mysql> select job_desc_title from postings where id=194582
->                           union all
->                     select email_address,first_name,last_name,home_phone_area,home_phone_num from accounts
->                      left join profiles on profiles.account_id=accounts.id
->                      where accounts.id=5;

ERROR 1222 (21000): The used SELECT statements have a different number of columns

What's the right version to do this job?

Answer 1

The two queries that you are unioning together must have the same number of output columns. Your first select contains one column, your first select contains five columns.

If you wish, you can pad the first select with nulls, as in:

select job_desc_title, null, null, null, null from postings where id=194582

Answer 2

yeah

select job_desc_title from postings where id=194582

and

select email_address,first_name,last_name,home_phone_area,home_phone_num from accounts

will not union as the need the same number of columns

See http://dev.mysql.com/doc/refman/5.0/en/union.html

UNION is used to combine the result from multiple SELECT statements into a single result set. The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)

as Robert Harvey states you can add blanks to mis-matching lines

select 
    job_desc_title,
    '' AS email_address,
    '' AS first_name,
    '' AS last_name,
    '' AS home_phone_area,
    '' AS home_phone_num 
from 
    postings 
where id=194582
union
select 
    '' AS job_desc_title,
    email_address,
    first_name,
    last_name,
    home_phone_area,
    home_phone_num 
from 
    accounts

But this will return you 2 rows for each match.

The more likely scenario is that you want to JOIN both datasets to form one row assuming a 1 to 1 relationship.

Answer 3

You're selecting job_desc_title form the first one and then email address, first name, last name, etc etc from the second one. This is not a union.

What you're looking to do is a join, and I suggest you read up on these. A union takes the results of two queries and combines them vertically. A join takes the results of two tables and combines them horizontally. Unions add rows, joins add columns. What you're trying to do is add a column (job_desc_title), not rows. Combining rows (i.e.-a union) takes the same columns to work.

I also think that you're using a left join when you should be using an inner join.

select 
    a.email_address,
    a.first_name,
    a.last_name,
    a.home_phone_area,
    a.home_phone_num,
    post.job_desc_title
from 
    accounts a
    inner join profiles p on 
        a.id=p.account_id
    inner join postings post on
        --I have no idea what the relationship is here, so I'm guessing
        p.posting_id = post.id
where 
    a.id=5

Hopefully this will get you somewhere near the right track.