Concatenate 2 rows into one in SQL-CodePudding

I have a very simple data structure with just 3 tables:

requests
employees
teams

Basically, what I need is to concatenate 2 different SQL selects into one (so that the query could return just one row).

If there's an employee associated with a request, then return their name (title) and concatenate it with ' / '.

Then, if there's a responsible team for the request, return its title too.

Finally, concatenate these 2 properties into one

The best I could do is:

(SELECT CONCAT(e.title, ' / ') FROM employees AS e
    WHERE e.id = (SELECT r.resposible_employee_id FROM requests AS r WHERE r.id = 1))
UNION    
(SELECT t.title FROM teams AS t
    WHERE t.id = (SELECT r.responsible_team_id FROM requests AS r WHERE r.id = 1))

But it returns 2 rows instead of one

I could do the concatenation separately by querying the DB twice, but that's not what I'm looking for. I want to query the DB just once.

For your convenience, here's a SQL Fiddle

The query should return the below just as one row

John Doe / Johns team

Thanks in advance

CodePudding user response：

Is something like the following what you're after?

select 
    Concat_Ws(' / ',
        (select title from employees e where e.id=r.resposible_employee_id),
        (select title from teams t where t.id=r.responsible_team_id)
    )
from requests r 
where id=1

See Modified SQL Fiddle

Also suspect respo(n)sible_employee_id is a typo

CodePudding user response：

You can perform a couple of outer joins with the related tables. For example:

SELECT 
  case when e.id is null
    then coalesce(t.title, 'no information')
    else case when t.id is null then e.title 
           else concat(r.resposible_employee_id, ' / ', t.title) 
         end
    end
FROM requests r
LEFT JOIN employees e on e.id = r.resposible_employee_id
LEFT JOIN teams t on t.id = r.responsible_team_id
WHERE r.id = 1

Result:

2 / Johns team

See running example at SQL Fiddle.

The CASE expression in the SELECT clause should account for all possible combinations of found/not found data in the related tables. Tweak as needed.