CODEWITHSUNDEEP
python-3.xsqlite
Another Dead Morty
Another Dead Morty

Reputation: 88

sqlite combine 2 queries from different tables to make one

I recently took to using sql again, the last time I used it was in microsoft access 2000 so please bear with me if I'm behind the times a little.

I have 2 pointless virtual currencies on my discord server for my players to play pointless games with. Both of these currencies' transactions are currently stored in individual tables.

I wish to sum up all the transactions for each player to give them a single current amount for each currency. Individually I can do this:

SELECT
 tblPlayers.PlayerID AS PlayerID,
 tblPlayers.Name AS Name,
 SUM(tblGorillaTears.Amount) 
FROM 
 tblPlayers 
INNER JOIN 
 tblGorillaTears
ON 
 tblPlayers.PlayerID = tblGorillaTears.PlayerID 
GROUP BY 
 tblPlayers.PlayerID;

and

SELECT
 tblPlayers.PlayerID AS PlayerID,
 tblPlayers.Name AS Name,
 SUM(tblKebabs.Amount) 
FROM 
 tblPlayers 
INNER JOIN 
 tblKebabs 
ON 
 tblPlayers.PlayerID = tblKebabs.PlayerID 
GROUP BY 
 tblPlayers.PlayerID;

What i need is a table that outputs the user name the id and the total for each currency on one row, but when i do this:

SELECT
 tblPlayers.PlayerID AS PlayerID,
 tblPlayers.Name AS Name,
 SUM(tblGorillaTears.Amount) AS GT,
 0 as Kebabs
FROM 
 tblPlayers 
INNER JOIN 
 tblGorillaTears
ON 
 tblPlayers.PlayerID = tblGorillaTears.PlayerID 
GROUP BY 
 tblPlayers.PlayerID
UNION 
SELECT
 tblPlayers.PlayerID AS PlayerID,
 tblPlayers.Name AS Name,
 0 as GP,
 SUM(tblKebabs.Amount) 
FROM 
 tblPlayers 
INNER JOIN 
 tblKebabs 
ON 
 tblPlayers.PlayerID = tblKebabs.PlayerID 
GROUP BY 
 tblPlayers.PlayerID;

the results end in a row for each player for each currency. How can i make it so both currencies appear in the same row?

Previously in MSAccess i was able to create two queries and then make a query of those two queries as if they were a table, but I cannot figure out how to do that in this instance. Thanks <3

Upvotes: 1

Views: 86

Answers (1)

PSK
PSK

Reputation: 17943

UNION will add new rows for sure, you can try like following query. 

SELECT TP.playerid                        AS PlayerID, 
       TP.NAME                            AS NAME, 
       (SELECT Sum(TG.amount) 
        FROM   tblgorillatears TG 
        WHERE  TG.playerid = TP.playerid) AS GT, 
       (SELECT Sum(TG.amount) 
        FROM   tblkebabs TG 
        WHERE  TG.playerid = TP.playerid) AS Kebabs 
FROM   tblplayers TP

Upvotes: 1

Related Questions