0

Basically, I have table a, which has a type and type_id and want to join on either two different tables where there is a hit.

So basically, if table b has a match, return the record or if table c has a match, return that record (essentially two inner joins with an OR); just one of them has to match. The below query seems to work... however I feel like this is a very naive solution that will actually result in me running into some issues. 

SELECT a.* FROM a LEFT JOIN b ON a.type_id = b.id AND b.user_id = 123 AND a.type = 'dog' LEFT JOIN c ON a.type_id = c.type_id AND c.user_id = 123 AND a.type = 'cat' WHERE b.id IS NOT NULL OR c.type_id IS NOT NULL;

Basically, I want all the records where the user exists in either table.

Improve this question

1 Answer 1

Reset to default
4

You could rewrite with OR and two EXISTS subqueries and compare performance with your query:

SELECT a.* FROM a WHERE EXISTS ( SELECT FROM b WHERE a.type_id = b.id AND b.user_id = 123 AND a.type = 'dog' ) OR EXISTS ( SELECT FROM c WHERE a.type_id = c.type_id AND c.user_id = 123 AND a.type = 'cat' ) ;

or:

SELECT a.* FROM a WHERE a.type = 'dog' AND EXISTS ( SELECT FROM b WHERE a.type_id = b.id AND b.user_id = 123 ) OR a.type = 'cat' AND EXISTS ( SELECT FROM c WHERE a.type_id = c.type_id AND c.user_id = 123 ) ;

Your query seems correct, assuming that there are unique constraints or indexes on b (user_id, id) or on b (id) - and c (user_id, type_id).

It does however looks somewhat obfuscated - it is not clear without careful reading that it restricts the result to rows that have WHERE a.type_id IN ('dog', 'cat').

Improve this answer
2
  • ah nice one! That does look nicer, however the language ORM im using (ecto for elixir) doesn't actually allow subqueries in where clauses (..yet). So just wondering if my solution is actually going to give any wrong results or if it should be ok? I'll mark the answer as solved beucase what you have posted will work for 99% of people Commented Nov 13, 2018 at 10:44
  • Your query seems fine, yes (assuming some unique constraints) on b and c. Commented Nov 13, 2018 at 11:54

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.