CODEWITHSUNDEEP
phpmysqljoin
kvadd
kvadd

Reputation: 3

How do I join two tables and then select two different columns?

I searched and tried to find a solution for this for a week now, but I'm stuck. I am trying to select two different columns in one table and comparing them with another database. This is my setup:

Table 1: fighters
  fightr_id     fightr_namn         fightr_record   fightr_nick
  1             Brock Lesnar        5-3-0 (0)       Brockan
  2             Frank Mir           5-1-0 (0)       Fettot
  3             Martin Johansson    2-33-1 (0)  
  4             Mirko Filipovic     12-22-0 (0)     cro cop

And this is the table I want as a "master":

Table 2: Matches
  match_id  gala_id     fightr_id1  fightr_id2   match_order
  3         14          1           2            0
  4         14          3           4            1

I want to outpot "fightr_id1" with a name from the table "fighters", as well as "fightr_id2" as a name, also from the table "fighters".

This is the output that I am trying to accive:

<?php while( $row = mysql_fetch_assoc( $document_get)) { ?>
    <tr><td><?php echo $row['match_ordning']; ?> </td> 
    <td>Fighter 1: <?php echo $row['fightr_name']; ?></td>
    <td>Fighter 2: <?php echo $row['fightr_namn']; ?></td></tr>

<?php } ?>

I have tried to read up about joining SQL, and I think this is probably the way to go. But I can't seem to figure out hos to code the select query. Can you help me?

Thank you in advance.

Upvotes: 0

Views: 67

Answers (1)

kgu87
kgu87

Reputation: 2057

select 
  m.match_id, 
  f1.fightr_namn as fighter1Name, 
  f2.fightr_namn as fighter2Name 
from matches m
inner join fighters f1 on m.fightr_id1 = f1.fightr_id
inner join fighters f2 on m.fightr_id2 = f2.fightr_id

http://sqlfiddle.com/#!2/31875f/3

Upvotes: 1

Related Questions