[SOLVED] Pulling from three tables

[Kemik]

Hello,

 

To display a list of challenges I need to access the data in:

 

ladder_challenges

-- challenge_id

-- ladder_id

-- datetime

-- clan_id_a (user's clan)

-- clan_id_b (opponent clan)

 

clans

-- clan_id (linked to clan_id_b)

-- name

 

ladder_config

-- ladder_id (linked to challenges ladder_id)

-- name

 

The WHERE would be: clan_id_a = $myclanid

 

Basically, I need to produce a HTML table with the following headers:

 

Challenge Info (w link contains challenge_id) || Ladder Name (w link contains ladder_id) Date & Time || Clan Name (w contains link clan id).

 

If you guys can come up with the SQL query I can come up with the table. I've only ever done a join using two tables, not three so I'm not sure where to start.

 

[Psycho]

You will actually need to use four tables in a sense. You will need to join the clans table twice to associate the clan names with clan_id_a and clan_id_b

 

Try this:

SELECT lc.*, ca.name as clan_a, cb.name as clan_b, config.name as ladder
FROM ladder_challenges lc
  LEFT JOIN clans ca ON lc.clan_id_a = ca.clan_id
  LEFT JOIN clans cb ON lc.clan_id_b = cb.clan_id
  LEFT JOIN ladder_config ladder ON lc.ladder_id = ladder.ladder_id
WHERE lc.clan_id_a = $myclanid

[Kemik]

Thanks alot

 

I can use this in other parts of my app too.