CODEWITHSUNDEEP
sqljoincount
Paul
Paul

Reputation: 654

SQL query with 2 joins and count

I've spent the last 8 hours trying to get this SQL query to work and I can't get it done. I am not very experienced, but am trying to get better at it.

I've got 2 tables:

Table giveaway
Columns:
id | userid | created | closed | winnerid | cancelled
1 | 7962290564 | 2014-07-26 20:32:22 | 2014-07-26 20:32:22 | 7962290566 | NULL
2 | 7962290565 | 2014-07-26 20:32:22 | 2014-07-26 20:32:22 | 7962290567 | NULL
3 | 7962290566 | 2014-07-26 20:32:22 | 2014-07-26 20:32:22 | 7962290567 | NULL
4 | 7962290566 | 2014-07-26 20:32:22 | 0000-00-00 00:00:00 | NULL | NULL

Table user
Columns:
id | steamid | joined | roleid | name 
1 | 7962290564 | 2014-07-26 20:32:22 | 1 | User a
2 | 7962290565 | 2014-07-26 20:32:22 | 4 | User b
3 | 7962290566 | 2014-07-26 20:32:22 | 4 | User c
4 | 7962290567 | 2014-07-26 20:32:22 | 4 | User d
5 | 7962290568 | 2014-07-26 20:32:22 | 4 | User e
6 | 7962290569 | 2014-07-26 20:32:22 | 5 | User f

What I'm after is a single result that gives a count of how many times someone has his id in giveaway.userid and how many times someone has his id in giveaway.winnerid. Also it has a few WHERE elements to filter out unwanted results.

It's something like:

SELECT user.steamid, user.name, count(giveaway.userid) nrgiven, count(giveaway.winnerid) nrwon
FROM user
WHERE user.roleid BETWEEN 1 AND 4
AND giveaway.cancelled IS NULL    
AND giveaway.closed IS NOT NULL
GROUP BY user.steamid

For these tables user.steamid = giveaway.userid
                 user.steamid = giveaway.winnerid

The result should be:

steamid | name | nrwon | nrgiven
7962290564 | User a | 0 | 1
7962290565 | User b | 0 | 1
7962290566 | User c | 1 | 2
7962290567 | User d | 2 | 0
7962290568 | User e | 0 | 0

Here are some examples of what I've tried, but none of them does a good counting.

SELECT user.steamid, user.name, count(giveaway.userid) nrgiven
FROM user
INNER JOIN giveaway
    ON user.steamid = giveaway.userid
    AND user.roleid BETWEEN 1 AND 4
    AND giveaway.closed IS NOT NULL
    AND giveaway.cancelled IS NULL
GROUP by user.steamid
ORDER BY nrgiven DESC;

---

SELECT user.roleid, user.steamid, user.name, a.nrgiven, b.nrwon 
FROM user, 
    ( SELECT count(user.steamid) nrgiven
    FROM user
    LEFT JOIN giveaway ON user.steamid = giveaway.userid 
    ) a,
    ( SELECT count(user.steamid) nrwon
    FROM user
    LEFT JOIN giveaway ON user.steamid = giveaway.winnerid 
    ) b;

---

SELECT user.steamid, user.name, a.nrwon
FROM user,
( SELECT count(giveaway.winnerid) nrwon
    FROM giveaway
    RIGHT JOIN user ON giveaway.winnerid = user.steamid
    WHERE user.roleid BETWEEN 1 AND 4
        AND giveaway.closed != 0
        AND giveaway.winnerid IS NOT NULL
    GROUP BY user.steamid

) a
GROUP BY user.steamid
ORDER BY steamid DESC;

Upvotes: 1

Views: 123

Answers (1)

bnp887
bnp887

Reputation: 5756

I was able to get your expected result table from that dataset with the following query:

SELECT user.steamid, user.name, COALESCE(winners.nrwon,0) as nrwon, COALESCE(nrgiveaways.nrgiven,0) as nrgiven
FROM user
LEFT JOIN (SELECT winnerid, COUNT(*) as nrwon FROM giveaway WHERE winnerid IS NOT NULL GROUP BY winnerid) as winners on winners.winnerid = user.steamid
LEFT JOIN (SELECT userid, COUNT(*) as nrgiven FROM giveaway GROUP BY userid) as nrgiveaways on nrgiveaways.userid = user.steamid
WHERE roleid in (1,2,3,4)

The winners and giveaways are found in their own tables and then added to the user table using left joins.

Upvotes: 1

Related Questions