Mysql union 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 | + -------------------- + ------------ + ----------- + --- -------------- + ---------------- + | newhjh@hotmail.com | Jianhua | He | 425 | 3584396 | + -------------------- + ------------ + ----------- + --- -------------- + ---------------- + 1 row in set (0.00 sec)
I need to combine the above 2 requests, but my trial completion 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 SELECT statements used have different number of columns
What's the correct version to do this job?
a source to share
You select the file job_desc_title, the first one and then the email address, first name, last name, etc. etc. from the second. This is not a union.
What you want to do is join and I suggest you read about it. A union takes the results of two queries and combines them vertically. A join joins the results of two tables and joins them horizontally. Unions add rows, join columns. What you are trying to do is add a column (job_desc_title), not rows. Concatenation of rows (i.e. Concatenation) allows the use of the same columns
I also think you are using left join when you should be using 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
Hope this gets you on the right track.
a source to share
The two queries you are merging together must have the same number of output columns. Your first choice has one column, your first choice has five columns.
If you want you can put the first selection with zeros like in:
select job_desc_title, null, null, null, null from postings where id=194582
a source to share
Yes
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 merge as the need for 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 returned results. The selected columns, listed in the corresponding positions of each SELECT statement, must be of the same data type. (For example, the first column selected by the first operator must be the same type as the first column selected by the other operators.)
as Robert Harvey states that you can add spaces to incorrectly matched strings
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 give you 2 rows back for each match.
A more likely scenario is that you want to JOIN both datasets to form a single row assuming a relationship between 1 and 1.
a source to share