very cool stuff. sadly, I don't think it'll work for my scenario. Forgot to mention I'm working on a setuid program, and what i'm seeing says ld_preload will be ignored if the setuid bit is set for a program.

That is annoying but also it seems like a pretty wise precaution.

I am guessing you don't have root on this box? Because it is still supposed to work if the libraries you are using are also setuid to that user, from what I've read

Definitely don't have root or the appropriate id, group or otherwise, on the account running the program.

And no, this isn't malicious, 100% educational.

I'm doing some SQL self-learning through Udacity and am stuck on one part of an assignment. I have created two tables for a tournament.

Players:
id | name
----+---------------
1 | player1
2 | player2
3 | player3

Matches:
matchid | loser | winner
---------+-------+--------
1 | 1 | 2
2 | 1 | 3
3 | 3 | 2

I am trying to write a query that will give me the following columns: Player ID, Name, Wins, Total Matches. I can figure out a query that provides the first 3, but can't figure out how to also include the last one. Currently I have this:

SELECT players.id, players.name, COUNT(matches.winner) as wins FROM players LEFT JOIN matches on players.id=matches.winner GROUP BY players.id

Any help would be greatly appreciated!

You don't need left join for this query. Please don't use left / right / outer unless the query specifically calls for it.

To get the second aggregate condition, you need a count(matches.matchid) in your query.

You do need a left join, if you want to show 0 as the count for players who have not played any matches (for the # won you need it if they haven't won any)

That's what I meant by "specifically calls for it."

Of course, if he has that requirement, he'll need coalesce.

Not for this case. All he wants from the left joined table is the counts. When rows don't exist, it just doesn't count them. If he wanted the game id or something, then yeah, he'd need coalesce.

Eh, you're right. I always get that backwards, lol.

But the point was really about being specific about what the query is supposed to return. I would interpret the question as a list of players who played and won a game, not a list of all players who are entered into a database, regardless if they played a game or played and never won a game. The second result is ambiguous, IMO.

I disagree but it's open to interpretation. If someone said "I want to know the number of wins for players in the game" I would consider "0 wins" as a valid result. I think many people would be confused if non-winning players just didn't appear in the results at all.

I mean the result is zero for players who:

-- never played a game

or

-- played a game and never won a game.

Both results are zero, but the intentions of the query may well be different. Granted, that's taken care of by the game-counting in the more complex query, so probably making a lame argument now.

Thanks for the replies, guys. I should have mentioned that the test-driver requires the '0' entry for players who haven't played any games.

Also, when I add a count(matches.matchid) column to my query, I get the following:

id | name | wins | totalMatches
----+---------------+------+-------
2 | player1 | 2 | 2
1 | player2 | 0 | 0
3 | player3 | 1 | 1

totalMatches should display '2' in each row. Any ideas?

There are various ways to do that but this would be my approach:

SELECT players.id, players.name, COALESCE(won.c, 0) AS won, COALESCE(won.c, 0) + COALESCE(lost.c, 0) AS played
FROM players
LEFT JOIN (SELECT winner AS id, COUNT(1) AS c FROM matches GROUP BY winner) won ON players.id = won.id
LEFT JOIN (SELECT loser AS id, COUNT(1) AS c FROM matches GROUP BY loser) lost ON players.id = lost.id

With two subqueries and two left joins, it's probably not the most efficient, but it's clear what it does.

Yeah I don't think you want count(matches.match_id), I think you probably want sum(players.id=matches.winner) or something like that. I think for some database systems that might work, for other's you might need an IF statement to turn that boolean into an integer, like

sum(if(players.id=matches.winner, 1, 0))
(this for sure would work on mysql)

Yeah, you can do it like that, but it looks gross to me. "players.id=matches.winner" belongs in a join condition, not in a select list. It's tortured SQL to put it in there. It may be more efficient, I'm not sure.

Edit: Also I think that inline if thing is just mysql. You want CASE players.id WHEN matches.winner...

It's different in all databases, I don't actually know what the standard SQL way would be, if there is one

And I guess agree to disagree, I don't like the way your query looks and it would probably take me a fair bit to work it out. Obviously since I came up with my way it seems pretty obvious/self explanatory to me

There's really no notion of where pieces of sql are "supposed" to be. x=y is just a boolean expression. Where clauses take boolean expressions but so does if() case() etc, which are all meant to be used in where clauses or selection terms

CASE is in the SQL-92 standard and will work in any implementation of SQL.

So the query would look like this:

SELECT players.id, players.name, SUM(CASE players.id WHEN matches.winner THEN 1 ELSE 0 END) AS won, COUNT(1) AS played
FROM players
LEFT JOIN matches ON players.id = matches.loser OR players.id = matches.winner
GROUP BY players.id

It's not gross I guess.

Thanks again guys, ChrisV's suggestion gets the job done. I feel a bit silly because I would have never come up with this on my own; lots more to learn I guess!

You get to know standard approaches to doing things. Here you need information about playerid from two columns in matches (winners and losers), so there are three possible approaches:

- Actually join to the table twice (my approach)
- Join to the table once, with an OR, and sort out the data you want in the SELECT (Rusty's approach)
- Transform the data into the format you want first and then join to it. That looks like this:

SELECT players.id, players.name, SUM(results.won) AS won, COUNT(results.id) AS played
FROM players
LEFT JOIN (
SELECT winner AS id, 1 AS won FROM matches
UNION ALL
SELECT loser AS id, 0 AS won FROM matches
) results ON players.id = results.id
GROUP BY players.id, players.name

I'd recommend understanding how all three work, you'll be building up your mental library of possible ways to solve problems.

Edit: Edited my query to group by players.name as well as players.id, otherwise name will be invalid in the SELECT. That goes for the second query I posted above as well.

Edit #2: Need COUNT(results.id) rather than COUNT(1) for "played", because the left join will result in one row for players who have not played and we want the result of that COUNT to be 0. (Again, that also goes for the second query above)

Think this should work:

I ran it against a test set that matched what you had and it seems to be correct.

(not sure if you got to aliasing and subselects)

The last 2 suggestions have been even more helpful, as they don't introduce any statements that i'm not yet familiar with.

Aliasing, subqueries and joins are still a bit confusing to me. I feel like I can understand each concept in isolation, but when I start trying to combine them in more complex queries I quickly become lost.

They all take a bit of time to get used to. You see I even made a mistake in the output of left join.

SQL is like anything else. You build and test one section at a time. Get the subquery tested, then test the raw output, then add the aggregates, etc.

For the most part, there is going to be multiple correct ways to do a query, meaning the answers will be equivalent. In your example, you could also use windowing or CTEs, but those aren't available on every system, plus probably a tad too advanced for where you are right now. Ultimately, it breaks down to what is going to be fastest on a given data set. It's very difficult to know without trying multiple queries and measuring.

Aliasing is very important to get used to. You just have to keep doing it until it gets easy. It's among the quickest indicator of how well the person knows SQL, IMO.

Subqueries are basically tables / views. Treat them the same.

This is a good explanation of joins (this is SQL Server syntax, which may not be the same as what you are using):

https://blog.codinghorror.com/a-visu...-of-sql-joins/

Oh man, that link is so helpful. Thanks! I love this stuff.

I've ran into another little snag. I have an EqualWins view for my Tournament Database which lists rows where player1 has the same number of wins as player 2. Now, I want to pair players with equal number of wins for the next round of the tournament, but I only want to select each player once.

EqualWins:

id1 | id2
-----+-----
50 | 49
56 | 50
56 | 49
56 | 54
54 | 50
54 | 49
52 | 51
55 | 51
55 | 52
55 | 53
53 | 51
53 | 52

I have messed around with LIMIT, GROUP BY, and DISTINCT but can't quite get the job done. Any tips?

EDIT: this is what I currently have:

SELECT DISTINCT a.id as id1, b.id as id2
FROM playerstandings AS a, playerstandings AS b
WHERE a.won=b.won AND a.id>b.id;