Postgres Query WHERE array

Question

I have problem with my Postgres Query.

I have 3 tables:

1) Carrier
  *id
  *name
  *telephone
  *address
  *comments


2) Transportlist
   *id
   *name

3) Carrier_transport
   *id
   *carrier_id
   *transport_list_id

For Example:

Carrier:           Transportlist:      Carrier_transport:
ID | name          ID  |  name         ID | carrier_id  | transport_list_id
 1 | ABC            1  | Car            1 |     1                1
 2 | XYZ            2  | Tir            2 |     1                2
 3 | 111            3  | Plane          3 |     1                3
                    4  | ferry          4 |     2                1
                                        5 |     3                4
                                        6 |     3                3
                                        7 |     3                2

I have to select only carriers which have transportlist Car AND Tir

I tried like:

Select  FROM Carrier c 
LEFT JOIN Carrier_transport ct ON (ct.carrier_id = c.id)
WHERE ct.transport_list_id IN (1,2) 
GROUP BY c.id
HAVING COUNT(*)>=2

But this solution is wrong.

Could somebody can help me?

But what if carrier x have one of type transport list like CAR and 10 different. It have COUNT >=2 but ONLY one element which I have to need — marczak
– marczak, Commented Jan 25, 2017 at 11:52
I don't see how the query is wrong. The combination of carrier_id and transport_list_id should be unique in carrier_transport. You select all records with transport_list_id 1 and 2 per carrier_id from the table and count if you got two records (i.e. both 1 and 2) for the carrier. What exactly does not work? — Thorsten Kettner
– Thorsten Kettner, Commented Jan 25, 2017 at 12:41
If the carrier has transport_list_id 1 and 3, then only the record with transport_list_id 1 gets selected (because 3 doesn't match transport_list_id IN (1,2)). One record => COUNT(*) = 1 => the carrier is not shown (HAVING COUNT(*)>=2). And this is exactly what you want, isn't it? — Thorsten Kettner
– Thorsten Kettner, Commented Jan 25, 2017 at 13:04
This is usually written, like HAVING COUNT(*) = 2 (or count a specific id -- it can never be greater than 2 because of your WHERE predicate). Also, your LEFT JOIN becomes an INNER one because of the same WHERE predicate. Apart from the obvious syntax error after SELECT, your query is fine: it should work. What is exactly wrong with it? — pozs
– pozs, Commented Jan 25, 2017 at 15:09

Clodoaldo Neto · Accepted Answer · 2017-01-25 12:22:16Z

1

select ct.carrier_id
from
    transportlist tl 
    inner join
    carrier_transport ct on ct.transport_list_id = tl.id
where ct.transport_list_id in (1,2) 
group by 1
having bool_or(tl.id = 1) and bool_or(tl.id = 2)

Aggregate functions

answered Jan 25, 2017 at 12:22

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

Collectives™ on Stack Overflow

Postgres Query WHERE array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related