CODEWITHSUNDEEP
sqlpostgresql
PrototypeX7
PrototypeX7

Reputation: 164

PostgreSQL - Getting wrong result with specific query

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?

Upvotes: 0

Views: 281

Answers (1)

jarlh
jarlh

Reputation: 44805

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

Upvotes: 2

Related Questions