View Full Version : 3 Table Link?

Lee Stevens
03-05-2009, 10:48 PM
Hey everyone,

I have three tables:

1). tbl_projects
2). tbl_discussions
3). tbl_files

The main is tbl_projects. This table will list all projects but when viewed it gets by pj_id.

When this is displaying i would like to list the discussions and files attached to the project. There primary key is pj_id also.

I would like it to be made into a list displayed on the projects detailed page.

Any help with the query building would be great thanks,

Old Pedant
03-06-2009, 12:19 AM
All in SQL??? Or in ASP with a MySQL DB? Or JSP with a MySQL DB? OR....

The SQL is easy:

SELECT P.x, P.y, D.a, D.b, NULL, NULL
FROM tbl_Projects AS P
LEFT JOIN tbl_discussions AS D ON P.pj_id = D.pj_id
SELECT P.x, P.y, NULL, NULL, F.m, F.n
FROM tbl_Projects AS P
LEFT JOIN tbl_files AS F ON P.pj_id = F.pj_id
ORDER BY P.pj_id, IF(D.a IS NULL, 2, 1 ), D.a, F.m

That will get duplicate tbl_Projects info, of course. But you just ignore all but the first (easy logic in your reporting language). Records with discussions info (if any) will be first and with file info will follow.

Lee Stevens
03-06-2009, 05:55 PM
Oh sorry it's in php, thank you for your reply. I'll have ago now.

Okay so now it's just gave me a massive long row of information, how would i post that information in threas like a forum.

Main Project at the top then list the discussions and files under?