CODEWITHSUNDEEP
sqlpostgresql
shaki
shaki

Reputation: 11

SQL query that combine 3 tables with a string like output

I need to to write a query that combine 3 different table and present them in a certain way.

Employees table has a joint column for join uses to 'Department' table (department_id) and 'Cars' table (emp_id).

I need to write a query that display the following data according to the user details (including the 'department' and 'cars' tables):

  1. Employees - All columns
  2. Department - Department column
  3. Cars - All columns but written as a string text with " , " seperating each column in the string.

I got the following query but I don't know how to print the "Cars" table columns as a string:

select
  Employees.*, 
  Departments.department, 
  Cars.* 
from Employee
join department on departments.id = employees.department_id 
join Cars on cars.emp_id = employees.id order by employees.id

Upvotes: 0

Views: 78

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can see the results as a record, which is almost the format you want:

select e.*, d.department, c 
from Employee e join
     department d
     on d.id = e.department_id join
     Cars c
     on c.emp_id = e.id
order by e.id;

Because you want the comma separator, you can get the result as a string using:

select e.*, d.department,
       substring(c::text, 2, length(c::text) - 2) as cars
from Employee e join
     department d
     on d.id = e.department_id join
     Cars c
     on c.emp_id = e.id
order by e.id;

Upvotes: 1

Nikhil
Nikhil

Reputation: 3950

I guess this is what you want:

select e.*,d.*,concat(c.col1,c.col2,c.col3.... ,',')
from Employees e,Departments d,Cars c
where 
e.department_id=d.department_id 
and d.emp_id=e.emp_id
order by e.emp_id;

Upvotes: 1

Related Questions