Combine predicates on two joined tables with OR

Question

I have three tables:

t_tbl
-----
t_id PK
t

p_tbl
-----
p_id PK
p

x_tbl
-----
x_id PK
x
t_id
p_id

My searches work separately:

Query 1:

SELECT x
FROM x_tbl xt
INNER JOIN
t_tbl tt
ON
xt.t_id = tt.t_id

Query 2:

SELECT x
FROM x_tbl xt
INNER JOIN
p_tbl pt
ON xt.p_id = pt.p_id

But I want to get xt.x if xt.t_id = tt.t_id OR xt.p_id = pt.p_id

score 1 · Answer 1 · answered Jul 31 '20 at 17:47

1

maybe UNION the results together?

SELECT x FROM x_tbl xt INNER JOIN t_tbl tt ON xt.t_id = tt.t_id
UNION
SELECT x FROM x_tbl xt INNER JOIN p_tbl pt ON xt.p_id = pt.p_id

answered Jul 31 '20 at 17:47

Chris Curvey

9,738
10
48
70

It is very unusual that this would return no results if each of your original queries did... – Hambone Jul 31 '20 at 17:53
Ya sorry. I had commented out the function call in my code so nothing was returned. There is a problem still though. x is type xml in my table. I get this error: psycopg2.errors.UndefinedFunction: could not identify an equality operator for type xml. It works seperately though without the union. – ntf Jul 31 '20 at 17:54
Can you post the DDL for these tables? – Hambone Jul 31 '20 at 18:00

score 1 · Accepted Answer · answered Jul 31 '20 at 17:51

1

I would try two left joins and then evaluate to see if one or the other was found:

select
  x.x
from
  x_tbl x
  left join p_tbl p on
    p.p_id = x.p_id
  left join t_tbl t on
    t.t_id = x.t_id
where
  p.i_id is not null or t.t_id is not null

answered Jul 31 '20 at 17:51

Hambone

15,600
8
46
69

score 0 · Answer 3 · answered Aug 01 '20 at 23:28

Any JOIN can multiply rows it it find multiple matches on the other side. (It's not clear from the description if that can happen in your db design.) It doesn't seem like you'd want that. And it gets very expensive quickly with many rows on the other side and/or multiple joins.

Seems like you want 1 row from x_tbl, where the conditions are met.
EXISTS is the safe way that never multiplies rows in x_tbl. It should also be fastest.

SELECT x.x
FROM   x_tbl x
WHERE  EXISTS (SELECT FROM t_tbl t WHERE t.t_id = x.t_id)
OR     EXISTS (SELECT FROM p_tbl p WHERE p.p_id = x.p_id)

This can happen with 2x LEFT JOIN:

Two SQL LEFT JOINS produce incorrect result

Combine predicates on two joined tables with OR

3 Answers3