Total by ID with multiple tables.

[NuGG]

I am trying to get a total of all the records for playerid from table1, table2 and table3 by playername.

There are 4 tables, table-1, table-2, table-3, and players.

I am not great with queries and I expect somebody here can instantly see where I am going wrong.
This is what I have:

SELECT
  `table-1`.playerid AS playerid1,
  COUNT(*) AS count,
  `table-2`.playerid AS playerid2,
  COUNT(*) AS count,
  `table-3`.playerid AS playerid3,
  COUNT(*) AS count,
FROM `players`
  INNER JOIN `table-1`
    ON `table-1`.playerid = `players`.playerid
  INNER JOIN `table-2`
    ON `table-2`.playerid = `players`.playerid
  INNER JOIN `table-3`
    ON `table-3`.playerid = `players`.playerid
WHERE `players`.playername = 'Player1'

Can anyone give me some pointers as to what I am doing wrong... Thanks.

[ryantroop]

Disclaimer: you made this into a mental exercise for me.. I think that all three below will give you the desired result. Anyone is welcome to correct me, especially on the last one...

If I understand your rule (Im grasping here), you want the number of records from all the tables where the playername is given.

so.. using subqueries, I would do it like this:

SELECT
  (SELECT COUNT(*) FROM table1 where playername = 'playername') +
  (SELECT COUNT(*) FROM table2 where playername = 'playername') +
  (SELECT COUNT(*) FROM table3 where playername = 'playername') +
  (SELECT COUNT(*) FROM players where playername = 'playername');

Alternatively...

I dont often do straight SQL with variables (I use stored procedures for all that good stuff...) so someone may have to fix this for me.

iCount int = 0;
SET iCount = COUNT(*) FROM table1 where playername = 'playername';
SET iCount = iCount + (SELCT COUNT(*) FROM table2 where playername = 'playername');
SET iCount = iCount + (SELECT COUNT(*) FROM table3 where playername = 'playername');
SET iCount = iCount + (SELECT COUNT(*) FROM players where playername = 'playername');

select iCount;

Lastly, and probably the most interesting...

SELECT
  p1.playerID,
  p2.playerID,
  p3.playerID,
  p.playername
from
  players as p
    left outer join table1 as p1 on p.playername = p1.playername
    left outer join table2 as p2 on p.playername = p2.playername
    left outer join table3 as p3 on p.playername = p2.playername
where
  p.playername = 'playername';

select FOUND_ROWS(); --total of all rows found from previous query.

EDIT: This last one did not work for me as expected. Your mileage may vary. Instead, try this:

select count(*) from (SELECT
                        p1.playerID,
                        p2.playerID,
                        p3.playerID,
                        p.playername
                      from
                        players as p
                        left outer join table1 as p1 on p.playername = p1.playername
                        left outer join table2 as p2 on p.playername = p2.playername
                        left outer join table3 as p3 on p.playername = p2.playername
                     where
                        p.playername = 'playername') as 'Count';

[NuGG]

Thats not really what im trying to do...

So far I have managed to total all of the records by a playername for 1 table at a time, like this...

SELECT
  `table-1`.playerid AS playerid,
  COUNT(*) AS count
FROM `lb-players`
  INNER JOIN `table-1`
    ON `players`.playerid = `table-1`.playerid
WHERE `players`.playername = "EXAMPLE"

However that is only the total number of records for table-1 by the playername "EXAMPLE", I need a total for all the records across multiple tables (table-1, table-2, and table-3).

The way around this is to run this query for each table and then calculate the final combined total with php, however I was thinking this is probably very inefficiant when it can probably be performed in one query...

Like I say its probably very simple but I have been struggaling with this for several days. When I try to add multiple JOINs to other tables as in my origianl post the query fails.

[ryantroop]

ahhh you want the individual counts output from each table...

so if I understand the result you want is:
playerid,
count references to player in t1
count references to player in t2,
count references to player in t3,
count references to player in player table?

ex:

3 'PlayerID',
21 'Count T1',
55 'Count T2',
66 'Count T3',
7 'Count Player Table'?