CODEWITHSUNDEEP
mysqlsql
Vikas Hire
Vikas Hire

Reputation: 628

How to get multiple column's rows value in a single column?

I have two table tblCompany and tblUser Both table contains email id fields, tblCompany contain 3 column as named contact1Email , contact2Email, contact3Email and tblUser contain a column named emailId. Unique field in both table is companyId. I want to get email of company and user's email those are belongs to that company. all those email ids I want in a single column. I tried it in basic way using below query:

SELECT DISTINCT contact1Email , contact2Email, contact3Email, emailId
FROM fm_company
INNER JOIN fm_users ON fm_company.companyId = fm_users.companyId
WHERE fm_company.companyId =33

this gives bellow result as shown in image enter image description here

you can see there emailId column contain user's email and contact1Email , contact2Email, contact3Email contain company email. I want all email in single column as name emailIds. How can it posible using sql query.?

I want a result like

|EmailIds         |
|-------------------
|[email protected]
|-------------------
|[email protected]
|-------------------
|[email protected]
|-------------------
|[email protected]
|-------------------
|[email protected]
|-------------------
|[email protected]
|-------------------

Upvotes: 0

Views: 705

Answers (2)

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY THIS: According to your question you want to display multiple columns email id in single column so I think you should use UNION OR UNION ALL to achieve the desired output in desired format as below:

SELECT DISTINCT contact1Email
FROM fm_company
INNER JOIN fm_users ON fm_company.companyId = fm_users.companyId
WHERE fm_company.companyId =33
UNION
SELECT DISTINCT contact2Email
FROM fm_company
INNER JOIN fm_users ON fm_company.companyId = fm_users.companyId
WHERE fm_company.companyId =33
UNION
SELECT DISTINCT contact3Email
FROM fm_company
INNER JOIN fm_users ON fm_company.companyId = fm_users.companyId
WHERE fm_company.companyId =33
UNION
SELECT DISTINCT emailId
FROM fm_company
INNER JOIN fm_users ON fm_company.companyId = fm_users.companyId
WHERE fm_company.companyId =33

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You can use concat_ws() to combine all into a single column:

SELECT DISTINCT CONCAT_WS(';', c.contact1Email , c.contact2Email, c.contact3Email, u.emailId) as emailIds
FROM fm_company c INNER JOIN
     fm_users u
     ON c.companyId = u.companyId
WHERE c.companyId = 33;

EDIT:

It occurs to me that you want a single email in each row, with multiple rows. If so:

select contact1Email as email
from company c
where c.companyId = 33
union -- on purpose to remove duplicates
select contact2Email as email
from company c
where c.companyId = 33
union
select contact3Email as email
from company c
where c.companyId = 33
union
select u.emailId
from fm_company c join
     fm_users u
     on c.companyId = u.companyId
where c.companyId = 33;

Upvotes: 2

Related Questions