Reading info from 4 tables to be displayed.

[WizyWyg]

Just need help with a query so I can display the results correctly

Im trying to join information from three separate tables so they can be displayed correctly on one page.

Currently i have this:

$q = "SELECT 
	genre.genre,
	games.* 
	FROM genre,games 
	WHERE genre.genreId=games.genreId 
	ORDER BY title";

Where it displays the information from two tables correctly

Here is how my tables are setup:

table console
sysId | system
1|pc
2|playstation
3|SNES

table employee
id|employee
1|None
2|Greg
3|Joe
4|Jane

table genre
genreId|genre
1|action
2|adventure
3|sports

table games
gameId|title|snumber|genreId|date|sysId|id
1|tony hawk|abc123|3|2000|2|3


So I want to display the information from the Games table but since genreId, sysId, and id are pulling information from the other three tables, I need to have the name of the genre, the name of the system and the name of the employee who has the game instead of their respective numbers.

I got it to show the GENRE instead of the id, but I can't figure out how to show the names for the rest.


The HTML produced:

<table>
<tr> 
      <td width=&quot;27%&quot;><b><font face=&quot;Arial, Helvetica, sans-serif&quot;>Game</font></b></td>
      <td width=&quot;13%&quot;><b><font face=&quot;Arial, Helvetica, sans-serif&quot;>Serial Number</font></b></td>
      <td width=&quot;17%&quot;><b><font face=&quot;Arial, Helvetica, sans-serif&quot;>Genre</font></b></td>
      <td width=&quot;13%&quot;><b><font face=&quot;Arial, Helvetica, sans-serif&quot;>Date Published</font></b></td>
      <td width=&quot;17%&quot;><b><font face=&quot;Arial, Helvetica, sans-serif&quot;>System / Console</font></b></td>
      <td width=&quot;13%&quot;><b><font face=&quot;Arial, Helvetica, sans-serif&quot;>On Loan To</font></b></td>
    </tr>
    <?php

while($myrow = mysql_fetch_array($result)) { 
     echo &quot;  <tr>
    <td><font face=\&quot;Arial, Helvetica, sans-serif\&quot; size=2>&quot;.$myrow[&quot;title&quot;].&quot;</font></td>
    <td><font face=\&quot;Arial, Helvetica, sans-serif\&quot; size=2>&quot;.$myrow[&quot;snumber&quot;].&quot;</font></td>
    <td><font face=\&quot;Arial, Helvetica, sans-serif\&quot; size=2>&quot;.$myrow[&quot;genre&quot;].&quot;</font></td>
	<td><font face=\&quot;Arial, Helvetica, sans-serif\&quot; size=2>&quot;.$myrow[&quot;date&quot;].&quot;</font></td>
	<td><font face=\&quot;Arial, Helvetica, sans-serif\&quot; size=2>&quot;.$myrow[&quot;system&quot;].&quot;</font></td>
	<td><font face=\&quot;Arial, Helvetica, sans-serif\&quot; size=2>&quot;.$myrow[&quot;employee&quot;].&quot;</font></td>
  </tr>\n&quot;; 
}

?>
  </table>

How do i get the query to reflect the need to show the names instead of their numbers? I dont know when to use LEFT JOIN or if its necessary.

$q = "SELECT 
	genre.genre,
	games.* 
	FROM genre,games 
	WHERE genre.genreId=games.genreId 
	ORDER BY title";

 

[WizyWyg]

Just got it to work:

$q = "SELECT genre.genre, console.system, employee.employee, games.* FROM games LEFT JOIN console ON console.sysId=games.sysId LEFT JOIN employee ON employee.id=games.id LEFT JOIN genre ON genre.genreId=games.genreId ORDER BY title";