How to Manage Many-to-Many Joins in SQL for HTML5and CSS3 Programming

Inner joins are a perfect way to implement one-to-many SQL relationships for HTML5 and CSS3 programming. If you look at ER diagrams, you often see many-to-many relationships, too. Of course, you also need to model them. Here's the secret: You can't really do it. The relational data model doesn't really have a good way to do many-to-many joins. Instead, you fake it. It isn't hard, but it's a bit sneaky.

You use many-to-many joins to handle listed data, such as the relationship between hero and power. Each hero can have any number of powers, and each power can belong to any number of heroes.

The inner join was easy because you just put a foreign key reference to the one side of the relationship in the many table. In a many-to-many join, there is no “one” side, so where do you put the reference? Leave it to computer scientists to come up with a sneaky solution.

Note that this table contains no reference to powers. You see a lot of powers, but no reference to heroes.

Here's the tricky part.

The results of this query may surprise you. The new table contains nothing but foreign keys. It doesn't make a lot of sense on its own, yet it represents one of the most important ideas in data.

Understanding link tables

The hero_power table is a brand new table, and it's admittedly an odd little duck:

• It contains no data of its own. Very little appears inside the table.

• It isn't about an entity. Most tables are about entities in your data. This one isn't.

• It's about a relationship. This table is actually about relationships between hero and power. Each entry of this table is a link between hero and power.

• It contains two foreign key references. Each record in this table links an entry in the hero table with one in the power table.

• It has a many-to-one join with each of the other two tables. This table has a many-to-one relationship with the hero table. Each record of hero_power connects to one record of hero. Likewise, each record of hero_power connects to one record of power.

• The two many-to-one joins create a many-to-many join. Here's the magical part: By creating a table with two many-to-one joins, you create a many-to-many join between the original tables!

• This type of structure is called a link table. Link tables are used to create many-to-many relationships among entities.

How to use link tables to make many-to-many joins

Here is a full-blown ER diagram of the hero data.

Link tables aren't really useful on their own because they contain no actual data. Generally, you use a link table inside a query or view:

SELECT
 hero.name AS 'hero',
 power.name AS 'power'
FROM
 hero, power, hero_power
WHERE
 hero.heroID = hero_power.heroID
AND
 power.powerID = hero_power.powerID;

Here are some thoughts about this type of query:

• It combines three tables. That complexity seems scary at first, but it's really fine. The point of this query is to use the hero_power table to identify relationships between hero and power. Note that the FROM clause lists all three tables.

• The WHERE clause has two links. The first part of the WHERE clause links up the hero_power table with the hero table with an inner join. The second part links up the power table with another inner join.

• You can use another clause to further limit the results. Of course, you can still add other parts to the AND clause to make the results solve a particular problem, but leave that alone for now.

Now you have results you can use.

Once again, this query is an obvious place for a view:

CREATE VIEW heroPowerView AS
 SELECT
 hero.name AS 'hero',
 power.name AS 'power'
 FROM
 hero, power, hero_power
 WHERE
 hero.heroID = hero_power.heroID
 AND
 power.powerID = hero_power.powerID;

Typically, you won't do your results exactly like this view. Instead, you display information for, say, Binary Boy, and you want a list of his powers. It isn't necessary to say Binary Boy three times, so you tend to use two queries (both from views, if possible) to simplify the task. For example, look at these two queries:

SELECT * FROM heroMissionView WHERE hero = 'binary boy';
SELECT power FROM heroPowerView WHERE hero = 'binary boy';

The combination of these queries gives you enough data to describe everything in the original table. Typically, you attach all this data together in your PHP code.

The code is standard PHP data access, except it makes two passes to the database:

<!doctype html>
<html lang="en">
<head>
 <meta charset="UTF-8">
 <title>showDetails.php</title>
 <style type = "text/css">
 dt {
  float: left;
  width: 4em;
  clear: left;
 }
 dd {
  float: left;
  width: 20em;
 }
 </style>
</head>
<body>
<?php
//connect
 try {
 $con= new PDO('mysql:host=localhost;dbname=haio', "haio", "haio");
 $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 //get most information for requested hero
 $hero = "binary boy";
 $query = <<<HERE
SELECT
 *
FROM
 heroMissionView
WHERE
 hero = '$hero'
HERE;
 print "<dl> n";
 $result = $con->query($query);
 $result->setFetchMode(PDO::FETCH_ASSOC);
 foreach ($result as $row){
  foreach ($row as $field => $value){
  print <<<HERE
 <dt>$field</dt>
 <dd>$value</dd>
HERE;
  } // end field foreach
 } // end row foreach
 print " <dt>powers</dt> n";
 print " <dd> n";
 //create another query to grab the powers
 $query = <<<HERE
SELECT
 power
FROM
 heroPowerView
WHERE hero = '$hero'
HERE;
 //put powers in an unordered list
 $result = $con->query($query);
 print " <ul> n";
 foreach ($result as $row){
  foreach ($row as $field => $value){
   print " <li>$value</li> n"; 
  } // end foreach
 } // end while loop
 print " </ul> n";
 print "</dd> n";
 print "</dl> n";
} catch(PDOException $e) {
 echo 'ERROR: ' . $e->getMessage();
} // end try
?>
</body>
</html>