1 Reply Latest reply on Aug 15, 2006 2:34 AM by draves

    joining 4 tables

    richy2424 Level 1
      Hi wonder if someone can help me,

      the code below is working if i have records in my POINTSTABLE

      but if there are no records in the POINTSTABLE i still want the TeamNames to display with zero as the points

      any ideas how i can do this, i dont have much experience with inner joins

      also i only want to show records where t.ClubAss = '#session.ClubName#'

      not sure where to put the clause in?
        • 1. Re: joining 4 tables
          draves Level 1
          <cfquery name="QueryOne" datasource="iluvcricket">
          SELECT t.TeamName,
          SUM(nvl(p.Points,0)) AS TotalPoints,
          (SELECT SUM(b.BonusPoints)
          FROM BonusTable b
          WHERE b.TeamID = t.TeamID) AS TotalAll
          FROM TeamsTable t
          INNER JOIN PlayerToTeam pt ON t.TeamID = pt.TeamID
          Left Outer JOIN PointsTable p ON pt.PlayerID = p.PlayerID
          GROUP BY t.TeamName, t.TeamID

          You need a left outer join if records may be missing but a result from the other table is to be displayed. The columns from the missing row should return all nulls. You need a function to test for the null and change it to a zero which is "nvl" in Oracle, "value" in db2, "IFNULL" in MySQL, "IsNull" or "Coalesce" in SQL Server, "IIf(IsNull(p.points),0,p.points)" in Access, etc.