MySQL Forums
Forum List  »  Newbie

JOIN on different tables based on column value
Posted by: Rutger Buijzen
Date: March 30, 2006 02:18AM

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.

Options: ReplyQuote


Subject
Written By
Posted
JOIN on different tables based on column value
March 30, 2006 02:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.