Re: JOIN on different tables based on column value

[Felix Geerinckx]

Rutger Buijzen wrote:

> In other words each record in POSTS needs to be joined to a record in either PERSONS_1 or
> PERSONS_2 depending on the value of PERSON_TYPE in POSTS.

SELECT
CASE
WHEN posts.person_type = 1 THEN p1.name
WHEN posts.person_type = 2 THEN p2.name
ELSE 'Cannot happen'
END Name
FROM posts
LEFT JOIN persons_1 p1 ON posts.person_id = p1.person_id
LEFT JOIN persons_2 p2 ON posts.person_id = p2.person_id

But may I ask why you have designed your database this way?

--
felix
Please use BBCode to format your messages in this forum.