CODEWITHSUNDEEP
sqlpostgresqljoin
aquamad96
aquamad96

Reputation: 63

How to JOIN these 3 tables together in SQL?

So I have 3 tables in total called companies_company c, companies_sharedtrainingunit cst and companies_trainingunit ct. The first JOIN I created joined c and cst on the same company id.

SELECT c.id as consuming_company_id, c.name as consuming_company_name, cst.id as channel_connection_id, cst.training_unit_id as channel_id
FROM companies_sharedtrainingunit cst
FULL JOIN companies_company c ON c.id = cst.company_id
ORDER BY company_id ASC

which yields a table with results like

consuming_company_id consuming_company_name channel_connection_id channel_id
1 company_1 2000 100

Now I want this joined table created above joined to the same info from table c and a new table ct ie

SELECT c.id as owned_company_id, c.name as owned_company_name, ct.id as channel_id, ct.name as channel_name, ct.description as channel_description
FROM companies_trainingunit ct
FULL JOIN companies_company c ON c.id = ct.company_id
ORDER BY c.id
owned_company_id owned_company_name channel_id channel_name channel_description
2 company_2 100 channel_100 description_100

I have tried joining these two queries but no luck :(

I know they need to be joined on their channel ids

In the end I am looking for my table to looking something like:

channel_connection_id consuming_company_id consuming_company_name channel_id channel_name channel_description owned_company_id owned_company_name
2000 1 company_1 100 channel_100 description_100 2 company_2

Upvotes: 0

Views: 76

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270843

I would expect a left join starting with companies to be sufficient to get the rows you want. Does this do what you want?

SELECT c.id as consuming_company_id, c.name as consuming_company_name,
       cst.id as channel_connection_id, cst.training_unit_id as channel_id,
       ct.id as channel_id, ct.name as channel_name, ct.description as channel_description
FROM companies_company c LEFT JOIN
     companies_sharedtrainingunit cst
     ON c.id = cst.company_id LEFT JOIN
     companies_trainingunit ct
     ON c.id = ct.company_id     
ORDER BY c.company_id ASC;

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95080

I find it hard to understand your data model. There are tables with "trainingunit" in their names, but there is no trainingunit ID. Instead there is a "channel_id" you want to join on. Why is that?

Then, you are using full outer joins to include companies without training units and training units without companies. Does that even make sense?

However, provided your queries are correct and you want to join them, just do so:

select *
from
(
  SELECT c.id as consuming_company_id, c.name as consuming_company_name, cst.id as channel_connection_id, cst.training_unit_id as channel_id
  FROM companies_sharedtrainingunit cst
  FULL JOIN companies_company c ON c.id = cst.company_id
) q1
(
  SELECT c.id as owned_company_id, c.name as owned_company_name, ct.id as channel_id, ct.name as channel_name, ct.description as channel_description
  FROM companies_trainingunit ct
  FULL JOIN companies_company c ON c.id = ct.company_id
) q2 using (channel_id)
order by channel_id;

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247790

Looks like you need to join with companies_company twice. Also, an inner join is sufficient:

SELECT cst.id as channel_connection_id,
       c1.id as consuming_company_id,
       c1.name as consuming_company_name,
       cst.training_unit_id as channel_id,
       ct.name as channel_name,
       ct.description as channel_description,
       c2.id as owned_company_id,
       c2.name as owned_company_name,
FROM companies_sharedtrainingunit cst
   JOIN companies_company c1 ON c1.id = cst.company_id
   JOIN companies_trainingunit ct ON cst.training_unit_id = ct.id
   JOIN companies_company c2 ON c2.id = ct.company_id
ORDER BY cst.company_id ASC;

Upvotes: 1

Related Questions