How can I merge the columns from two tables into one column output

Question

I have two tables with similar information. First Table is suppliers and other table is suppliers_contacts. Is there a way to merge the two columns into one?

My suppliers table looks like this:

id | contacts_name   | email
-- | ------          | -------
1  | sujith          | sujith@sujith
2  | mark            | 
3  | naveen          | naveen@naveen

and supplier_contacts table, like this:

suppliers_id | name      | email
--           | ----      |
1            | sujith    | sujith1@sujith    
2            | user1     | user1@user1
2            | user2     | user2@user2
3            | naveen1   | naveen1@naveen1
3            | naveen    | naveen2@naveen

And I want to get something like this:

contacts_name | name      | email 
--            | ------    | ------- 
sujith        | sujith    | sujith@sujith
sujith        | sujith    | sujith1@sujith
user1         | user1     | user1@user1
user2         | user2     | user2@user2
naveen        |           | naveen@naveen
naveen1       | naveen1   | naveen1@naveen1
naveen        | naveen    | naveen2@naveen

All the email id should come in one Column from both the tables.

I tried the query below:

SELECT
    suppliers.name, 
    supplier_contacts.name AS name1, 
    supplier_contacts.email AS sup_c_email 
FROM suppliers 
JOIN supplier_contacts 
    ON suppliers.id = supplier_contacts.suppliers_id

Can anyone help me on this? Basically i want the emails from both the tables to come under one column also should show contacts_name and name from both the tables. If any name or contacts_name is NULL also its fine.

Sorry that was a type error. I corrected the question. Basically i want the emails from both the tables to come under one column. — , Jun 19 '18 at 10:14
@Sanju Can you try using COALESCE(suppliers.email, supplier_contacts.email) in your SELECT ? COALESCE will give you only one non null value — Praveen E, Jun 19 '18 at 10:17
Yes i tried with COALESCE also, but at times if both the values are there the resultset should show both the emails. How to handle such scenario? — , Jun 19 '18 at 10:20
It should show null in name column for email `sujith@sujith` in your expected output like `naveen@naveen` — M Khalid Junaid, Jun 19 '18 at 10:55
The resultset am expecting is email list. It should merge the email list from two tables and show in one column. — , Jun 19 '18 at 10:58

score 1 · Answer 1 · answered Jun 19 '18 at 10:18

1

SELECT suppliers.name, supplier_contacts.name AS name1, supplier_contacts.email AS 
sup_c_email 
FROM supplier 
INNER JOIN suppliers_contacts 
ON suppliers.email = supplier_contacts.email

I hope this will help you

answered Jun 19 '18 at 10:18

Razz

452
6
9

This is the correct answer, if I understood the question correctly. If not you could do a Left Outer join on the email comparison, to display any results where one email doesn't have a partner. – Morten Bork Jun 19 '18 at 10:21

score 0 · Answer 2 · answered Jun 19 '18 at 10:17

0

I think what you are looking for is a full outer join

Although it returns large result-sets.

Great read at this link

answered Jun 19 '18 at 10:17

Codebender

195
1
10

Sadly myssql does not have full outer join. – P.Salmon Jun 19 '18 at 10:18
Sorry about that. Take a look at [this](https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql). It seems you can emulate them. – Codebender Jun 19 '18 at 10:52

Krish · Answer 3 · 2018-06-19T10:54:32.533

0

SELECT s.contacts_name , sc.name , sc.email
FROM supplier as s JOIN suppliers_contacts as sc ON s.id = sc.suppliers_id

For more explainations visit below link hope you get help from it https://www.tutorialspoint.com/mysql/mysql-using-joins.htm

edited Jun 19 '18 at 10:54

answered Jun 19 '18 at 10:41

Krish

21
3

Both the tables have email id fields. It should come in one column. – Jun 19 '18 at 10:45

M Khalid Junaid · Answer 4 · 2018-06-20T05:16:25.687

0

You could use union for your case, first get list of all emails the then do a left join with suppliers_contacts

select
  t.contacts_name,
  s.name,
  t.email
from (
  select contacts_name, email from suppliers where email is not null
  union
  select name, email from suppliers_contacts
) t
left join suppliers_contacts s on t.email = s.email 
order by t.contacts_name;

Demo

As per your last comment The resultset am expecting is email list. It should merge the email list from two tables and show in one column, you can simply use union query

select contacts_name, email from suppliers where email is not null
union
select name contacts_name, email from suppliers_contacts
order by contacts_name;

Demo

Edit * I want to show all the email address from both the tables with the contacts_name and name from each table. If names are NULL its fine*

select
t.contacts_name,
sc.name supplier_contact_name,
s.contacts_name supplier_name,
t.email
from (
  select contacts_name, email from suppliers where email is not null
  union
  select name, email from suppliers_contacts
) t
left join suppliers_contacts sc on t.email = sc.email 
left join suppliers s on t.email = s.email 
order by t.contacts_name;

Demo

edited Jun 20 '18 at 05:16

answered Jun 19 '18 at 10:58

M Khalid Junaid

63,861
10
90
118

The union query is working fine. I just wanna know how will i display contact_name field from the suppliers table along with the said fields – Jun 20 '18 at 04:14
@Sanju can you edit your question and add desired result set ? – M Khalid Junaid Jun 20 '18 at 04:59
Yes its mentioned in the question. I want to show all the email address from both the tables with the contacts_name and name from each table. If names are NULL its fine. – Jun 20 '18 at 05:05
@Sanju see last query in my answer – M Khalid Junaid Jun 20 '18 at 05:16
Actually when am running this query the screen is hanging, so i guess there is some problem. So am trying with the second query a simple Union where i can show the names from both the tables. Thank you @Khalid – Jun 20 '18 at 05:47

How can I merge the columns from two tables into one column output

4 Answers4