#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    668
    Rep Power
    6

    MySQL JOINS w/ multiple reference


    Howdy y'all. This should hopefully be quick. My main 'contract' table holds data and IDs to refence items from other tables. This 'contract' table has 2 user id columns. One contains the ID of the individual who set up the contract, while the other is who sold it. My first one I have referencing a name from the 'user' table just fine. How would I then use a 2nd ID during the same query to fetch a seperate name from the 'user' table?

    Full:
    Code:
    SELECT servicecontracts.`ID`,
           customers.`Customer Name`,
           machines.`Serial Number`,
           userpass.`Username`,
           servicecontracttype.`ContractName`,
           servicecontracts.`CUID`,
           servicecontracts.`CTID`,
           servicecontracts.`Activated`
    FROM servicecontracts
    LEFT JOIN (customers,
               machines,
               servicecontracttype,
               userpass) ON (customers.`ID`=servicecontracts.`CID`
                             AND machines.`ID`=servicecontracts.`MID`
                             AND servicecontracttype.`ID`=servicecontracts.`PTID`
                             AND userpass.`ID`=servicecontracts.`PUID`)
    General idea:
    Code:
    SELECT userpass.`Username`,
           servicecontracttype.`ContractName`,
           servicecontracts.`CUID`,
           servicecontracts.`CTID`
    FROM servicecontracts
    LEFT JOIN (userpass) ON (userpass.`ID`=servicecontracts.`PUID`)
    CUID and CTID are the IDs I wish to use. Username and ContractName are using my PUID and PTID. Is this possible in a single query?

    PUID = ID to userpass.`Username`
    PTID = ID to servicecontracttype.`ContractName`
    CUID = ID to userpass.`Username`
    CTID = ID to servicecontracttype.`ContractName`
    Last edited by Triple_Nothing; August 10th, 2013 at 04:06 PM.
  2. #2
  3. D'oh!
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Location
    East Coast
    Posts
    75
    Rep Power
    72
    It's possible, all you have to do is alias your tables and fields and treat the aliases like they're completely different tables.

    With this much work though, you may be better served moving some of this processing off to your code side (if you have one) and dropping some of these joins. If this gets to be a large table, your queries are going to start taking a while.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    668
    Rep Power
    6
    Ah, aliases. *click* Heh. Thanks.

    EDIT: For the sake of throwing in the fixed code...

    Code:
    SELECT m1.`ID`,
           c.`Customer Name`,
           m2.`Serial Number`,
           u1.`Username`,
           t1.`ContractName`,
           u2.`Username`,
           t2.`ContractName`,
           m1.`Activated`
    FROM servicecontracts AS m1
    LEFT JOIN customers AS c ON c.`ID`=m1.`CID`
    LEFT JOIN machines AS m2 ON m2.`ID`=m1.`MID`
    LEFT JOIN userpass AS u1 ON u1.`ID`=m1.`PUID`
    LEFT JOIN servicecontracttype AS t1 ON t1.`ID`=m1.`PTID`
    LEFT JOIN userpass AS u2 ON u2.`ID`=m1.`CUID`
    LEFT JOIN servicecontracttype AS t2 ON t2.`ID`=m1.`CTID`;
    Last edited by Triple_Nothing; August 11th, 2013 at 01:54 PM.

IMN logo majestic logo threadwatch logo seochat tools logo