Getting data from three tables the most efficient way

Discussion in 'PHP' started by PoPSiCLe, Jun 8, 2009.

0
  1. #1
    Hey.

    I have three tables.

    One main table, lets call it "table_1" and two subtables, lets call them "table_2" and "table_3".

    In "table_1" I have the following columns:
    ID
    char_ID
    char_name
    char_race
    char_group
    char_desc

    In "table_2" I have:
    race_ID
    race_name

    and in "table_3" I have:
    group_ID
    group_name

    What I need is to get the data from "table_1" but substitute whatever is in "char_race" and "char_group" with the matching "race_name" and "group_name" from "table_2" and "table_3".

    Anyone?

    I'm thinking I need something like this:
    SELECT * FROM table_1 SUBSTITUTE table_1.char_race WITH table_2.race_name WHERE table_2.race_ID = table_1.char_race AND SUBSTITUTE table_1.char_group WITH table_3.group_name WHERE table_3.group_ID = table_1.char_group

    Anyone able to make me a SELECT-statement that works?

    Much appreciated!
     
    PoPSiCLe, Jun 8, 2009 IP
  2. clarky_y2k3

    clarky_y2k3 Well-Known Member

    Messages:
    114
    Likes Received:
    9
    Best Answers:
    0
    Trophy Points:
    108
    #2
    This can be achieved using table joins:-

    
SELECT `t1`.`ID` , `t1`.`char_name` , `t1`.`char_ID` , `t2`.`race_name` , `t3`.`group_name` , `t1`.`char_desc`
FROM `table1` t1
LEFT JOIN `table2` t2 ON `t1`.`char_race` = `t2`.`race_ID`
LEFT JOIN `table3` t3 ON `t1`.`char_group` = `t3`.`group_ID`
    Code (markup):
     
    clarky_y2k3, Jun 8, 2009 IP