Troubles with query (5 table JOIN)

[bundy]

Hi,

 

I'm having trouble displaying a list of events. I want to join 5 tables but I can't figure out how to do it.

This is the table where I log my events, lets call it log

ID

|

TYPE

|

UID

|

PID

|

TID

|

CID

|

DATE

0

|

1

|

0

|

2

|

null

|

null

|

some date

1

|

2

|

1

|

null

|

23

|

null

|

some date

2

|

3

|

2

|

null

|

null

|

2

|

some date

(uid = user id, pid = project id, tid = task id, cid = comment id)

 

 

This is table 'projects':

ID|TITLE|DATE

0|Project 1|some date

2|Project 2|some date

23|Project 3|some date

 

This is table 'tasks':

ID|PID|TITLE|DATE

0|0|Task 1|some date

13|1|Task 2|some date

23|0|Task 3|some date

 

This is table 'comments':

ID|PID|CONTENT|DATE

0|12|Comment on project.id 12|some date

1|2|Comment on project.id 2|some date

2|23|Comment on project.id 23|some date

 

This is table 'users':

ID|NAME

0|Tom

1|Sam

2|Erik

 

So my idea was to query each row of table log and displaying a message for each type of event.

 

Example:

if type (column type in table log) of event is 1 somebody created a new project thus the message would be:

• Tom has created a new project called 'Project 2'
  

 

If type is 2, the message would be:

• Sam has created a task called 'Task 3' for project called 'Project 1'
  

 

If type is 3, the message would be:

• Erik has commented on a project called 'Project 3'
  

 

I hope I explained my problem clearly enough.

 

Greets

[Barand]

Something like

SELECT g.type, u.name, g.date, p.title as project, null as comment_task
FROM log g
  JOIN users u ON g.uid = u.id
  JOIN projects p ON g.pid = p.id
WHERE g.type = 1
UNION
SELECT g.type, u.name, g.date, p.title as project, t.title as comment_task
FROM log g
  JOIN users u ON g.uid = u.id
  JOIN tasks t ON g.tid = t.id
  JOIN projects p ON t.pid = p.id
WHERE g.type = 2
UNION
SELECT g.type, u.name, g.date, p.title as project, c.content as comment_task
FROM log g
  JOIN users u ON g.uid = u.id
  JOIN comments c ON g.cid = c.id
  JOIN projects p ON c.pid = p.id
WHERE g.type = 3

 

-->[pre]

+------+------+------------+-----------+-------------------------+

| type | name | date      | project  | comment_task            |

+------+------+------------+-----------+-------------------------+

|    1 | Tom  | 2008-01-01 | Project 2 | NULL                    |

|    2 | Sam  | 2008-01-02 | Project 3 | Task 3                  |

|    3 | Erik | 2008-01-03 | Project 3 | Comment on project.id 3 |

+------+------+------------+-----------+-------------------------+[/pre]