CODEWITHSUNDEEP
sqlpostgresql
Rodrigo
Rodrigo

Reputation: 3278

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

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    |

Upvotes: 0

Views: 32

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269453

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.

Upvotes: 0

Omri Bahat Treidel
Omri Bahat Treidel

Reputation: 563

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.

Upvotes: 1

Alan
Alan

Reputation: 1428

You'll need left join instead of inner join

Upvotes: 0

Related Questions