Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: 3 Table Link?

  1. #1
    Regular Coder
    Join Date
    Aug 2006
    Location
    UK, London, Dartford
    Posts
    221
    Thanks
    3
    Thanked 14 Times in 14 Posts

    3 Table Link?

    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,
    Lee.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,542
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    All in SQL??? Or in ASP with a MySQL DB? Or JSP with a MySQL DB? OR....

    The SQL is easy:
    Code:
    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
    UNION
    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.

  • #3
    Regular Coder
    Join Date
    Aug 2006
    Location
    UK, London, Dartford
    Posts
    221
    Thanks
    3
    Thanked 14 Times in 14 Posts
    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.

    So
    Main Project at the top then list the discussions and files under?
    Last edited by Lee Stevens; 03-06-2009 at 05:15 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •