PostgreSQL using data from multiple joins to query data from another table

Question

I have 3 tables I want to use for 1 SQL query.

customers:
 - id
 - first_name
 - last_name

customer_professional:
 - id
 - customer_id
 - professional_id

company_customer:
 - id
 - customer_id
 - company_id

The query I'm trying to run is to grab all the customers where the professional_id = ? and the company_id = ?. This is the query I've come with it so far.

SELECT * 
FROM   customers c 
       INNER JOIN customer_professional cp 
               ON c.id = cp.customer_id 
       INNER JOIN company_customer cc 
               ON c.id = cc.customer_id 
WHERE  ( cp.professional_id = '36ec09ec-6b07-45e3-ae2d-a77bfe381bab' ) 
        OR ( cc.company_id = '36ec09ec-6b07-45e3-ae2d-a77bfe381baa' ) 
ORDER  BY "first_name" 
LIMIT  50;

This query is returning only the customers that are in both the customer_professional and company_customer. The query I'm trying to run should return all customers even if they are only in 1 of the junction tables.

SIDE NOTE:

I know what you're thinking this should only be 2 tables. I agree with you.

UPDATE

Data I'm expecting:

customers table:
|  id  |  first_name  |  last_name  |
-------------------------------------
|  1   |  peter       |  griffin    |
|  2   |  meg         |  griffin    |
|  3   |  stewie      |  griffin    |
|  4   |  louis       |  griffin    |
|  5   |  bryan       |  griffin    |

customer_professional table:
|  id  |  customer_id  |  professional_id  |
--------------------------------------------
|  1   |  1            |  1                |
|  2   |  2            |  1                |
|  3   |  5            |  1                |

company_customer table:
|  id  |  customer_id  |  company_id  |
---------------------------------------
|  1   |  3            |  2           |
|  2   |  4            |  2           |
|  3   |  5            |  2           |

So with the query I want to run I have the company_id and the professional_id and I want to return the customers that belong to either a professional or the company.

query:

SELECT * 
FROM   customers c 
       INNER JOIN customer_professional cp 
               ON c.id = cp.customer_id 
       INNER JOIN company_customer cc 
               ON c.id = cc.customer_id 
WHERE  ( cp.professional_id = '1 ) 
        OR ( cc.company_id = 2 ) 
ORDER  BY "first_name" 
LIMIT  50;

This only returns:

|  id  |  first_name  |  last_name  |
-------------------------------------
|  5   |  bryan       |  griffin    |

The query I'm trying to run should return

|  id  |  first_name  |  last_name  |
-------------------------------------
|  1   |  peter       |  griffin    |
|  2   |  meg         |  griffin    |
|  3   |  stewie      |  griffin    |
|  4   |  louis       |  griffin    |
|  5   |  bryan       |  griffin    |

What's the result you're expecting? Please add the fields you want as a result. — Dan
– Dan, Commented Oct 31, 2017 at 22:58

Omri Bahat Treidel · Accepted Answer · 2017-10-31 23:03:32Z

1

This is what inner join does: it keeps only the intersection between the tables. That is only the entries that appear in both. I think what you are after is left join which keeps all the entries which allows you to filter them using the where clause.

answered Oct 31, 2017 at 23:03

Omri Bahat Treidel

5834 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rodrigo Over a year ago

Alan answered the question right before. But thank you.

Alan · Accepted Answer · 2017-10-31 22:59:47Z

0

You'll need left join instead of inner join

answered Oct 31, 2017 at 22:59

Alan

1,4282 gold badges20 silver badges24 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2017-10-31 23:04:12Z

0

Presumably, you want something like this:

SELECT c.* 
FROM customers c INNER JOIN
     customer_professional cp 
     ON c.id = cp.customer_id AND
        cp.professional_id = '36ec09ec-6b07-45e3-ae2d-a77bfe381bab' INNER JOIN
     company_customer cc 
     ON c.id = cc.customer_id AND
        cc.company_id = '36ec09ec-6b07-45e3-ae2d-a77bfe381baa' 
WHERE cp.customer_id IS NOT NULL OR cc.customer_id IS NOT NULL
ORDER  BY "first_name" 
LIMIT  50;

First, I find your question confusing. The text says "and", but you appear to want "or". Second, the two additional tables are junction tables, not pivot tables.

The changes are:

This only selects columns from the customers table. I'm not sure that you really need the other columns.
The conditions on the ids are moved to ON clauses.
The joins are changed to LEFT JOINs.
The WHERE clause checks that there is at least one match.

answered Oct 31, 2017 at 23:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Rodrigo Over a year ago

This only returns customers that are in both junction tables.

Gordon Linoff Over a year ago

@Rodrigo . . . It should return customers that are in either junction table. That is why the WHERE clause has an OR.

Collectives™ on Stack Overflow

PostgreSQL using data from multiple joins to query data from another table

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related