Screwed up join

[mrbaseball34]

In this SQL:

  $sql = "SELECT p.*,
                 g.gamenum,
                 t.teamname,
                 MONTHNAME(g.gamedate) AS gamemonname,
                 DAYNAME(g.gamedate)   AS gamedayname,
                 DATE_FORMAT(g.gamedate, '%D') AS gamedaynum,
                 l.playername as playername,
                 l.jersey as jersey
          FROM pitchcounts p
          INNER JOIN games g ON g.gamenum = p.gamenum
          RIGHT JOIN players l on l.teamid = p.teamid
          RIGHT JOIN teams t on p.teamid = t.id
          WHERE WEEK(g.gamedate) = WEEK('".$thiswk."')
          ORDER BY p.teamid;";

I am getting 30 records where I should only be getting 6. There are 6 records in the pitchcounts table, 20 records in the players table and 30 records in the teams table.

I am not that versed in JOINS can someone help me out here and explain what is happening and how to make this return the 6 records in the pitchcounts table with the other data as needed?
Removing the RIGHT JOINS produces the same result.

[bpat1434]

You're joining right instead of left it seems....

Okay, so think of it this way: When you join tables, you should specify which table is being joined on which index or column. You do that with the ON clause. Now, let's take for example this simplified JOIN query:

tableA LEFT JOIN tableB ON tableA.id = tableB.id

Now, an INNER JOIN without the ON or USING or WHERE clauses will create a "carteasian" table with every row of each table being available and when there is an non-match in one table, NULLs will be inserted instead of values. So if tableA has 50 rows, and tableB has 60 rows, there will be 10 rows of NULLs where tableB extends past tableA.

A LEFT JOIN will take the left table in the ON clause (in our example tableA) and grab only those rows from tableB that match up with whatever column in tableA. So this will get rid of the 10 extra NULL rows that an INNER JOIN gives us.

A RIGHT JOIN will take the right table and grab only those rows from tableA that match up with tableB. So a RIGHT JOIN will have 10 extra NULL rows like the INNER JOIN.

Now, let's look at your example. You want 6 records and the only table that has 6 records is pitchcounts, so I'll assume that the pitchcounts table will always be the one you want to use.

So, you are using that as your main table, great start. Now you want to join the games table onto the pitchcounts table. You're probably better off using a LEFT JOIN and limiting it to only the 6 rows (the ones you want). So that part of the query would go something like:

LEFT JOIN games AS g ON p.gamenum = g.gamenum

Now you want to add the player information to it. Your current join is fine. Notice how the pitchcounts table (p) is on the right of the equals sign? So that will effectively keep our list to only 6 rows.

Finally you want to join the teams table. Here's where you're getting your 30 rows. Notice how the p.teamid is on the left of the equals? So you will be joining the pitchcounts table onto the teams table which is where the 30 rows comes in. You need to either switch p.teamid with t.id so they're on the other side of the equals, or just change it from a RIGHT to a LEFT join.

Everything else should be okay.