PostgreSQL - Getting wrong result with specific query

Question

I have a little problem with my SQL-Query. In my database, there are 3 tables table1, table2, table3 with some fields. In this case only up to 3 fields (id, field1, field2) per table are relevant, i tried to keep it simple in the query below.

I have the following query:

SELECT t1.field1, t1.field2
FROM table1 t1, table2 t2, table3 t3
WHERE t3.field1 = 'param1'
    AND t3.id = t1.t3_id
    AND t1.field2 = 'param2'
    OR t1.field2 IS NULL
    AND t2.field1 = 'param3'
    AND t2.id = t1.t2_id

For some reason i always get the wrong result, so i decided to add

SELECT t2.id

and

AND t2.id = 1

to test it

SELECT t1.field1, t1.field2, t2.id
FROM table1 t1, table2 t2, table3 t3
WHERE t3.field1 = 'param1'
    AND t3.id = t1.t3_id
    AND t1.field2 = 'param2'
    OR t1.field2 IS NULL
    AND t2.field1 = 'param3'
    AND t2.id = t1.t2_id
    AND t2.id = 1

Now in my result set, there are still entries with t2.id != 1. How can i prevent this?

jarlh · Accepted Answer · 2016-03-02 09:05:23Z

2

It's the OR that causes confusion. Put it within parentheses:

SELECT t1.field1, t1.field2, t2.id
FROM table1 t1, table2 t2, table3 t3
WHERE t3.field1 = 'param1'
    AND t3.id = t1.t3_id
    AND (t1.field2 = 'param2' OR t1.field2 IS NULL)
    AND t2.field1 = 'param3'
    AND t2.id = t1.t2_id
    AND t2.id = 1

Re-write with modern, explicit JOIN syntax:

SELECT t1.field1, t1.field2, t2.id
FROM table1 t1
JOIN table2 t2 ON t2.id = t1.t2_id
JOIN table3 t3 ON t3.id = t1.t3_id
WHERE t3.field1 = 'param1'
    AND (t1.field2 = 'param2' OR t1.field2 IS NULL)
    AND t2.field1 = 'param3'
    AND t2.id = 1

answered Mar 2, 2016 at 9:05

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

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

2 Comments

PrototypeX7 Over a year ago

Thank you, that helped, but i have another question. I keep getting a lot of equal entries (in my case 85). Whats the best way to handle this? Add DISTINCT to the query?

jarlh Over a year ago

Yes, use SELECT DISTINCT to remove duplicates.

Collectives™ on Stack Overflow

PostgreSQL - Getting wrong result with specific query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related