PostgreSQL – How to Merge Join Results

join;postgresqlselect

I have 2 tables with common columns and would like to know if it's possible to request this 2 tables by merging the results.

Table "job" : parent_id, target_id, start_date, end_date, jobposition_id

Table "study" : parent_id, target_id, start_date, end_date

At this moment if I do something like :

SELECT *
FROM user AS u 
LEFT JOIN study AS s ON s.parent_id=u.id OR s.target_id=u.id 
LEFT JOIN job AS j ON j.parent_id=u.id OR j.target_id=u.id
WHERE u.id=2;

I got a resultset with every common column doubled (I have 2 times parent_id, target_id, start_date, end_date).

How can I get a simple resultset with only one column parent_id, etc (and a jobposition_id column set to NULL for every study result) ?

Thanks

Best Answer

I hope to have fully understood your request. With this query you get a concatenation of tables (a UNION) with all records.

SELECT parent_id, target_id, start_date, end_date, NULL AS jobposition_id FROM study
UNION ALL
SELECT parent_id, target_id, start_date, end_date, jobposition_id FROM job

You can play with UNION to obtain the merging concept you have in mind. Here's a link to the official documentation (PostgreSQL 9.4): UNION clause. I think that you would like to remove duplicates (if there are duplicate entries in both tables), so probably the UNION DISTINCT is right for you.

Related Solutions

Combining Data from Multiple Tables in MySQL

I splitted the problem into two basic SELECTs, one for members and the another one for guests and I join both later. MySql's GROUP_CONCAT aggregate funtion is very useful here too.

http://sqlfiddle.com/#!9/58786/1

SELECT 
TT1.day as day
,TT1.booking_id as booking_id
,TT1.member_id as member_id
,CASE 
  WHEN TT2.guest_ids IS NULL 
  THEN 0 
  ELSE TT2.guest_ids 
  END as guest_id
,TT1.start_date as start_date
,TT1.end_date as end_date
,CASE 
  WHEN TT2.guest_ids IS NULL
  THEN TT1.member_name
  ELSE CONCAT(TT1.member_name, ', ', TT2.guests_name)
  END as names
FROM
(
SELECT 
Min(day) as day
, booking_id
, member_id
, Members.id
, CONCAT(first_name,' ',last_name) as member_name
, start_date
, end_date   
FROM Bookings
INNER JOIN Members
ON Bookings.member_id = Members.id
WHERE member_id <> 0
GROUP BY booking_id, member_id, guest_id, start_date, end_date
) AS TT1
LEFT JOIN
(
SELECT 
booking_id
, GROUP_CONCAT(guest_id SEPARATOR ', ') as guest_ids
, GROUP_CONCAT(CONCAT(first_name, ' ', last_name) SEPARATOR ', ') as guests_name
FROM
(
SELECT
booking_id
, guest_id
FROM Bookings
WHERE guest_id <> 0
GROUP BY booking_id, guest_id
) AS T1
INNER JOIN Guests
ON T1.guest_id = Guests.id
GROUP BY booking_id
) AS TT2
ON TT1.booking_id = TT2.booking_id
ORDER BY TT1.day, TT1.booking_id

MySQL – Joining Second Table Based on Third Table with Conditions

There are several problems with the query

The select list is missing.
The join syntax needs ON, not WITH: a JOIN b ON <some condition>
There is a missing on condition (what you correctly identified as arbitrary/random join between Contractor and ContractorJob).
There are column names mismatches between the code and the design: (Job.contractor_job vs. Job.id). Which is the correct one?

How to write it with LEFT JOIN / IS NULL. The parentheses are redundant, placed only for making the joins more clear:

select 
    c.*
from 
        Contractor as c
    left join
        ( 
            ContractorJob as cj
        inner join 
            Job as j 
        on  
                j.id = cj.job_id 
            and j.start_date < :start_date 
            and j.end_date > :end_date
        )
    on 
        c.id = cj.contractor_id
where 
    cj.contractor_id is null ;

How to write it with NOT EXISTS which I find easier to grasp:

select c.*
from Contractor as c
where not exists
    ( select * 
      from ContractorJob as cj
        inner join Job as j 
        on  j.id = cj.job_id 
      where j.start_date < :start_date 
        and j.end_date > :end_date
        and c.id = cj.contractor_id
    ) ;

Best Answer

Related Solutions

Combining Data from Multiple Tables in MySQL

MySQL – Joining Second Table Based on Third Table with Conditions

Related Question