Postgresql query between two tables returns multiple entries for one Id

Question

I'm trying to make a query that links the name of the client that's on the client table to the cargo that has the corresponding id. I'm doing this query:

SELECT c.id, c.status, c.client_id, c.dt, c.cargo_date, c.cargo_hour, c.origin, c.destination, c.code_name, c.download_at, c.download_hour, c.weight, c.ramp_id, c.truck_id, c.driver_id, c.cargo_return, c.returning, c.description, cl.name, cl.id 
from cargos c, 
     clients cl 
where c.status = 'planner' and (c.destination != 'SANTIAGO') 
  or  (c.destination = 'SANTIAGO' and c.returning is not NULL) 
 AND  c.client_id = cl.id 
order by COALESCE(c.returning, c.id);

but when I do that it returns me each cargo with every client in my client's table, isn't the AND c.client_id = cl.id supposed to make the result unique per id?

Pleaser provide sample data, desired results and a clear explanation of the logic. Also learn to use proper, explicit, standard, readable JOIN syntax. — Gordon Linoff
– Gordon Linoff, Commented Dec 10, 2020 at 14:27

Brad · Accepted Answer · 2020-12-10 14:25:11Z

1

It looks like you may be missing brackets around one of your AND statements to include one of the OR's AND all the others:

So you do status AND one or the other in the brackets I added, AND Client_id = cl.ID

When you use AND and OR in your where clause you have to make sure you put braces around the groups you want or else it can really mess up your logic.

where 
c.status = 'planner' 
and 
    -- I added the opening and closing bradckets around the OR statement
    (
        (c.destination != 'SANTIAGO') 
        or (c.destination = 'SANTIAGO' and c.returning is not NULL) 
    )
AND c.client_id = cl.id

edited Dec 10, 2020 at 14:25

answered Dec 10, 2020 at 14:22

Brad

3,7253 gold badges32 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

Postgresql query between two tables returns multiple entries for one Id

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