JOIN on different tables based on column value
Hi
Is it possible to perform a join on a certain table based on a value in the record itself?
Suppose I've got three tables, POSTS, PERSONS_1 And PERSONS_2
Table PERSONS_1 contains personal data of persons of type 1 and
Table PERSONS_2 contains personal data of persons of type 2
Table POSTS contains posts made by persons from either PERSONS_1 or PERSONS_2 indicated by the PERSON_TYPE value
Table POSTS: (ID,PERSON_ID,PERSON_TYPE, POST)
(1,1,1,'post by person 1 from PERSONS_1')
(2,1,2,'post by person 1 from PERSONS_2')
Table (PERSONS_1: ID,NAME)
(1,'person 1 from table PERSONS_1')
Table (PERSONS_2: ID,NAME)
(1,'person 1 from table PERSONS_2')
Now I want to create a query listing all POSTS and the names of the persons they were posted by:
POST,NAME
'post by person 1 from PERSONS_1','person 1 from table PERSONS_1'
'post by person 1 from PERSONS_2','person 1 from table PERSONS_2'
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.
Is this possible, and if so what would be the SQL for this query?
Thanks in advance.