Multiple tables in SELECT query

Question

I'm trying to select columns from multiple tables. Here's the code I have:

SELECT 
  Sum(IIf((tblChain.InitialZone="1"),1,0)) AS SCCH, 
  tblStatHistory.HomeTeam, 
  Sum(IIf(([Pentagraph]="HANDB") Or ([Pentagraph]="HANBB") Or ([Pentagraph]="HBCL") Or 
    ([Pentagraph]="KBBW") Or ([Pentagraph]="KBCL") Or ([Pentagraph]="KBIN") Or 
    ([Pentagraph]="KBLO") Or ([Pentagraph]="KBSH") Or ([Pentagraph]="KKBW") Or 
    ([Pentagraph]="KKCL") Or ([Pentagraph]="KKGKN") Or ([Pentagraph]="KKBW") Or 
    ([Pentagraph]="KKGKO") Or ([Pentagraph]="KKGKP") Or([Pentagraph]="KKIN") Or 
    ([Pentagraph]="KKLO") Or ([Pentagraph]="KKSH"),1,0)) AS D,
  Sum(IIf(([Pentagraph]="KBBW") Or ([Pentagraph]="KBCL") Or ([Pentagraph]="KBIN") Or
    ([Pentagraph]="KBLO") Or ([Pentagraph]="KBSH") Or ([Pentagraph]="KKBW") Or
    ([Pentagraph]="KKCL") Or ([Pentagraph]="KKGKN") Or ([Pentagraph]="KKBW") Or
    ([Pentagraph]="KKGKO") Or ([Pentagraph]="KKGKP") Or ([Pentagraph]="KKIN") Or
    ([Pentagraph]="KKLO") Or ([Pentagraph]="KKSH"),1,0)) AS K,
  Sum(IIf(([Pentagraph]="KBBW") Or ([Pentagraph]="KKBW"),1,0)) AS K_Back,
  Sum(IIf(([Pentagraph]="MACOO") Or ([Pentagraph]="MACOP") Or ([Pentagraph]="MAPAO") Or
    ([Pentagraph]="MAPAP") Or ([Pentagraph]="MAOL") Or ([Pentagraph]="MAUNO") Or
    ([Pentagraph]="MAUNP"),1,0)) AS M,
  Sum(IIf(([Pentagraph]="MAERO") Or ([Pentagraph]="MAERP") Or ([Pentagraph]="MAUNO") Or
    ([Pentagraph]="MAUC") Or ([Pentagraph]="MAUNP") Or ([Pentagraph]="MAOL"),1,0)) AS UM,
  Sum(IIf(([Pentagraph]="MACOO") Or ([Pentagraph]="MACOP"),1,0)) AS CM,
  Sum(IIf(([Pentagraph]="HANDB") Or ([Pentagraph]="HANBB") Or ([Pentagraph]="HBCL"),1,0)) AS H,
  Sum(IIf(([Pentagraph]="CBCL") Or ([Pentagraph]="BUCL") Or ([Pentagraph]="TICL"),1,0)) AS C,
  Sum(IIf(([Pentagraph]="CBCL"),1,0)) AS C_CSB,
  Sum(IIf(([Pentagraph]="BUCL") Or ([Pentagraph]="TICL"),1,0)) AS C_BUTI,
  Sum(IIf(([Pentagraph]="CBFP") Or ([Pentagraph]="BUFP") Or ([Pentagraph]="TIFP"),1,0)) AS FP,
  Sum(IIf(([Pentagraph]="BUFP") Or ([Pentagraph]="TIFP"),1,0)) AS FP_BUTI,
  Sum(IIf(([Pentagraph]="CBFP"),1,0)) AS FP_CSB,
  Sum(IIf(([Pentagraph]="CBHO") Or ([Pentagraph]="BUHO") Or ([Pentagraph]="TIHO"),1,0)) AS HO,
  Sum(IIf(([Pentagraph]="BUHO") Or ([Pentagraph]="TIHO"),1,0)) AS HO_BUTI,
  Sum(IIf(([Pentagraph]="CBHO"),1,0)) AS HO_CSB,
  Sum(IIf(([Pentagraph]="IN50"),1,0)) AS I50,
  Sum(IIf(([Pentagraph]="GOAL"),1,0)) AS GOAL,
  Sum(IIf(([Pentagraph]="BEHI"),1,0)) AS BHND,
  Sum(IIf(([Pentagraph]="TACKN") Or ([Pentagraph]="TACKO") Or ([Pentagraph]="TACKP") Or
    (([Pentagraph]="RDTDD") And ([ROLE]=1)) Or (([Pentagraph]="TACKL") And ([ROLE]=1)) Or
    (([Pentagraph]="RDTAK") And ([ROLE]=1)) Or (([Pentagraph]="DISP") And ([ROLE]=1)),1,0)) AS T,
  Sum(IIf(([Pentagraph]="GEHAN") Or ([Pentagraph]="GEHAO") Or ([Pentagraph]="GEHAP") Or ([Pentagraph]="GEHCN") Or ([Pentagraph]="GEHCO") Or([Pentagraph]="GEHCP") Or
    ([Pentagraph]="GERU") Or ([Pentagraph]="GELON") Or ([Pentagraph]="GELOO") Or ([Pentagraph]="GELOP") Or ([Pentagraph]="GELCN") Or ([Pentagraph]="GELCO") Or ([Pentagraph]="GELCP"),1,0)) AS GB
FROM 
  tblStatHistory
WHERE 
  (((tblStatHistory.Period)=IIf(:QTR1,1) Or (tblStatHistory.Period)=IIf(:QTR2,2) Or
  (tblStatHistory.Period)=IIf(:QTR3,3) Or (tblStatHistory.Period)=IIf(:QTR4,4)) AND
  ((tblStatHistory.LogicalZone)=IIf(:ZONEF50,"F") Or (tblStatHistory.LogicalZone)=IIf(:ZONEAM,"S") Or
  (tblStatHistory.LogicalZone)=IIf(:ZONEDM,"R") Or (tblStatHistory.LogicalZone)=IIf(:ZONED50,"D")) AND
  ((tblStatHistory.MatchID)=:GameID) AND ((tblStatHistory.PeriodSecs)>:Seconds))
GROUP BY 
  tblStatHistory.HomeTeam
ORDER BY 
  tblStatHistory.HomeTeam DESC;

This part of the very first line is the second table I'm trying to access

Sum(IIf((tblChain.InitialZone="1"),1,0)) AS SCCH

What's happening is if I try write the value of "SCCH" to a variable, it's spitting back that the SCCH field is empty. It also kills the output from the rest of the code, reporting 0 instead of the count of the query.

Any suggestions on how to best implement this?

EDIT: Here is the original code

Procedure TGame.UpdateTeamStats;
  var
    Query: TADOQuery;
    P,Z,T: integer;
  begin
    try
      Query := TADOQuery.Create(nil);
      Query.Connection := connection;
      Query.Close;
      Query.SQL.Add('SELECT tblStatHistory.HomeTeam, Sum(IIf(([Pentagraph]="HANDB") Or ');
      Query.SQL.Add('([Pentagraph]="HANBB")  Or ([Pentagraph]="HBCL") Or ([Pentagraph]="KBBW") Or ([Pentagraph]="KBCL") Or ');
      Query.SQL.Add('([Pentagraph]="KBIN") Or ([Pentagraph]="KBLO") Or ([Pentagraph]="KBSH") Or ');
      Query.SQL.Add('([Pentagraph]="KKBW") Or ([Pentagraph]="KKCL") Or ([Pentagraph]="KKGKN") Or ');
      Query.SQL.Add('([Pentagraph]="KKBW") Or ([Pentagraph]="KKGKO") Or ([Pentagraph]="KKGKP") Or ');
      Query.SQL.Add('([Pentagraph]="KKIN") Or ([Pentagraph]="KKLO") Or ([Pentagraph]="KKSH"),1,0)) AS D, ');
      Query.SQL.Add('Sum(IIf(([Pentagraph]="KBBW") Or ([Pentagraph]="KBCL") Or ([Pentagraph]="KBIN") Or ');
      Query.SQL.Add('([Pentagraph]="KBLO") Or ([Pentagraph]="KBSH") Or ([Pentagraph]="KKBW") Or ');
      Query.SQL.Add('([Pentagraph]="KKCL") Or ([Pentagraph]="KKGKN") Or ([Pentagraph]="KKBW") Or ');
      Query.SQL.Add('([Pentagraph]="KKGKO") Or ([Pentagraph]="KKGKP") Or ([Pentagraph]="KKIN") Or ');
      Query.SQL.Add('([Pentagraph]="KKLO") Or ([Pentagraph]="KKSH"),1,0)) AS K, ');

      Query.SQL.Add('Sum(IIf(([Pentagraph]="MACOO") Or ([Pentagraph]="MACOP") Or ([Pentagraph]="MAPAO") Or ');
      Query.SQL.Add('([Pentagraph]="MAPAP") Or ([Pentagraph]="MAOL") Or ([Pentagraph]="MAUNO") Or ');
      Query.SQL.Add('([Pentagraph]="MAUNP"),1,0)) AS M, ');

      Query.SQL.Add('Sum(IIf(([Pentagraph]="MAERO") Or ([Pentagraph]="MAERP") Or ([Pentagraph]="MAUNO") Or ');
      Query.SQL.Add('([Pentagraph]="MAUC") Or ([Pentagraph]="MAUNP") Or ([Pentagraph]="MAOL"),1,0)) AS UM, ');

      Query.SQL.Add('Sum(IIf(([Pentagraph]="MACOO") Or ([Pentagraph]="MACOP"),1,0)) AS CM, ');

      Query.SQL.Add('Sum(IIf(([Pentagraph]="KBBW") Or ([Pentagraph]="KKBW"),1,0)) AS K_Back, ');

      Query.SQL.Add('Sum(IIf(([Pentagraph]="HANDB") Or ([Pentagraph]="HANBB") Or ([Pentagraph]="HBCL"),1,0)) AS H, ');
      Query.SQL.Add('Sum(IIf(([Pentagraph]="CBCL") Or ([Pentagraph]="BUCL") Or ([Pentagraph]="TICL"),1,0)) AS C, ');
      Query.SQL.Add('Sum(IIf(([Pentagraph]="CBCL"),1,0)) AS C_CSB, ');
      Query.SQL.Add('Sum(IIf(([Pentagraph]="BUCL") Or ([Pentagraph]="TICL"),1,0)) AS C_BUTI, ');
      Query.SQL.Add('Sum(IIf(([Pentagraph]="CBFP") Or ([Pentagraph]="BUFP") Or ([Pentagraph]="TIFP"),1,0)) AS FP, ');
      Query.SQL.Add('Sum(IIf(([Pentagraph]="BUFP") Or ([Pentagraph]="TIFP"),1,0)) AS FP_BUTI, ');
      Query.SQL.Add('Sum(IIf(([Pentagraph]="CBFP"),1,0)) AS FP_CSB, ');
      Query.SQL.Add('Sum(IIf(([Pentagraph]="CBHO") Or ([Pentagraph]="BUHO") Or ([Pentagraph]="TIHO"),1,0)) AS HO, ');
      Query.SQL.Add('Sum(IIf(([Pentagraph]="BUHO") Or ([Pentagraph]="TIHO"),1,0)) AS HO_BUTI, ');
      Query.SQL.Add('Sum(IIf(([Pentagraph]="CBHO"),1,0)) AS HO_CSB, ');
      Query.SQL.Add('Sum(IIf(([Pentagraph]="IN50"),1,0)) AS I50, ');
      Query.SQL.Add('Sum(IIf(([Pentagraph]="GOAL"),1,0)) AS GOAL, ');
      Query.SQL.Add('Sum(IIf(([Pentagraph]="BEHI"),1,0)) AS BHND, ');
      Query.SQL.Add('Sum(IIf(([Pentagraph]="TACKN") Or ([Pentagraph]="TACKO") Or ([Pentagraph]="TACKP") Or ');
      Query.SQL.Add('(([Pentagraph]="RDTDD") And ([ROLE]=1)) Or (([Pentagraph]="TACKL") And ([ROLE]=1)) Or ');
      Query.SQL.Add('(([Pentagraph]="RDTAK") And ([ROLE]=1)) Or (([Pentagraph]="DISP") And ([ROLE]=1)),1,0)) AS T, ');
      Query.SQL.Add('Sum(IIf(([Pentagraph]="GEHAN") Or ([Pentagraph]="GEHAO") Or ([Pentagraph]="GEHAP") Or ([Pentagraph]="GEHCN") Or ([Pentagraph]="GEHCO") Or([Pentagraph]="GEHCP") Or ');
      Query.SQL.Add('([Pentagraph]="GERU") Or ([Pentagraph]="GELON") Or ([Pentagraph]="GELOO") Or ([Pentagraph]="GELOP") Or ([Pentagraph]="GELCN") Or ([Pentagraph]="GELCO") Or ([Pentagraph]="GELCP"),1,0)) AS GB');
      Query.SQL.Add('FROM tblStatHistory');
      Query.SQL.Add('WHERE (((tblStatHistory.Period)=IIf(:QTR1,1) Or (tblStatHistory.Period)=IIf(:QTR2,2) Or ');
      Query.SQL.Add('(tblStatHistory.Period)=IIf(:QTR3,3) Or (tblStatHistory.Period)=IIf(:QTR4,4)) AND ');
      Query.SQL.Add('((tblStatHistory.LogicalZone)=IIf(:ZONEF50,"F") Or (tblStatHistory.LogicalZone)=IIf(:ZONEAM,"S") Or ');
      Query.SQL.Add('(tblStatHistory.LogicalZone)=IIf(:ZONEDM,"R") Or (tblStatHistory.LogicalZone)=IIf(:ZONED50,"D")) AND ');
      Query.SQL.Add('((tblStatHistory.MatchID)=:GameID) AND ((tblStatHistory.PeriodSecs)>:Seconds))');
      Query.SQL.Add('GROUP BY tblStatHistory.HomeTeam');
      Query.SQL.Add('ORDER BY tblStatHistory.HomeTeam DESC;');

      with Query.Parameters.AddParameter do
      begin
        DataType := ftinteger;
        name:= 'GameID';
      end;

      with Query.Parameters.AddParameter do
      begin
        DataType := ftinteger;
        name:= 'Seconds';
      end;

      with Query.Parameters.AddParameter do
      begin
        DataType := ftinteger;
        name:= 'QTR1';
      end;

      with Query.Parameters.AddParameter do
      begin
        DataType := ftinteger;
        name:= 'QTR2';
      end;

      with Query.Parameters.AddParameter do
      begin
        DataType := ftinteger;
        name:= 'QTR3';
      end;

      with Query.Parameters.AddParameter do
      begin
        DataType := ftinteger;
        name:= 'QTR4';
      end;

      with Query.Parameters.AddParameter do
      begin
        DataType := ftinteger;
        name:= 'ZONEF50';
      end;

      with Query.Parameters.AddParameter do
      begin
        DataType := ftinteger;
        name:= 'ZONEAM';
      end;

      with Query.Parameters.AddParameter do
      begin
        DataType := ftinteger;
        name:= 'ZONEDM';
      end;

      with Query.Parameters.AddParameter do
      begin
        DataType := ftinteger;
        name:= 'ZONED50';
      end;

      Query.Parameters.ParamByName('GameID').Value := GameID;
      for P := 0 to 4 do
      begin
        if updatePeriodCheck(p) then
        begin
          for Z := 0 to 7 do
          begin
            SetParameters(P,Z,0,Query.Parameters);
            Query.Open;
            Query.First;
            for T := 0 to 3 do
            begin
              if T = 1 then Query.Next;
              if T < 2 then
                Stats[P,Z].Team[T] := PopulateStatsRecord(Query)
              else if T = 2 then // Difference
                Stats[P,Z].Team[T] := SubtractStatsRecords(Stats[P,Z].Team[1],Stats[P,Z].Team[0])
              else if T=3 then begin // Difference Z v Z
                if Z = 0 then
                  Stats[P,Z].Team[T] := Stats[P,Z].Team[2]
                else if Z = 1 then
                  Stats[P,Z].Team[T] := SubtractStatsRecords(Stats[P,1].Team[1],Stats[P,4].Team[0])
                else if Z = 2 then
                  Stats[P,Z].Team[T] := SubtractStatsRecords(Stats[P,2].Team[1],Stats[P,3].Team[0])
                else if Z = 3 then
                  Stats[P,Z].Team[T] := SubtractStatsRecords(Stats[P,3].Team[1],Stats[P,4].Team[0])
                else if Z = 4 then
                  Stats[P,Z].Team[T] := SubtractStatsRecords(Stats[P,4].Team[1],Stats[P,1].Team[0])
                else if Z = 5 then
                  Stats[P,Z].Team[T] := Stats[P,Z].Team[2]
                else if z = 6 then
                  Stats[P,Z].Team[T] := SubtractStatsRecords(Stats[P,6].Team[1],Stats[P,7].Team[0])
                else if z = 7 then
                  Stats[P,Z].Team[T] := SubtractStatsRecords(Stats[P,7].Team[1],Stats[P,6].Team[0]);
              end;
            end;
            Query.Close;
          end;
        end;
      end;
    finally
      Query.Free;
    end;
  end;

Why did you hide the most important part inside of delphi code? And please add the database system you are using — Sir Rufo, May 19 '14 at 06:39
Maybe I missed that, but where did you `JOIN` the other tables? — Sir Rufo, May 19 '14 at 06:41
@Sir Rufo - There isn't one. I assume the select from tblChain is meant to be a subselect and I assume this is MsAccess but as far as SQL goes, this is not ANSI SQL. Rewriting it to ANSI SQL might also solve OP's problem. I have no idea how MsAccess resolves al of this. — Lieven Keersmaekers, May 19 '14 at 06:43
@LievenKeersmaekers This is the original sql statement stripped from the posted source, so there is no room for "meant to be". It has to be clear when opening that statement. — Sir Rufo, May 19 '14 at 06:47
Apologies for the confusion, I should have clarified. It is indeed MS Access which is being accessed by Delphi. I'll post the original code that is written in the delphi project. — Brodie Messenger, May 19 '14 at 07:11
The original delphi code is too long for a comment - please refer to the original post. — Brodie Messenger, May 19 '14 at 07:14
Your added "original" code did not contain the `tableChain` line in the statement. What happend to this? — Sir Rufo, May 19 '14 at 10:19
Why don't you create a 'pentagraph' table with a flag field - this way you can remove all of the statements like *or [pentagraph] = "BUHO"* and then you might be able to see what is happening. Also, the IIF function in the 'select' statement has three parameters whereas it has only two in the 'where' statement - is this ok? — No'am Newman, May 19 '14 at 11:39

Multiple tables in SELECT query

0 Answers0