0

I have the following query:

SELECT
    A.player, A.score, B.kills, C.deaths, D.killed AS most_killed, D.kills AS most_killed_count, E.player AS most_killed_by, E.kills AS most_killed_by_count
FROM
(SELECT player, score FROM pvpr_scores WHERE player = 'Neutronix' AND milestone = 'default') AS A
LEFT JOIN (SELECT player, COUNT(*) AS kills FROM pvpr_kills WHERE player = 'Neutronix' AND milestone = 'default') AS B ON B.player= A.player
LEFT JOIN (SELECT killed, COUNT(*) AS deaths FROM pvpr_kills WHERE killed = 'Neutronix' AND milestone = 'default') AS C ON C.killed= A.player
LEFT JOIN (SELECT player, killed, COUNT(*) AS kills FROM pvpr_kills WHERE player = 'Neutronix' GROUP BY killed ORDER BY kills DESC LIMIT 1) AS D ON D.player= A.player
LEFT JOIN (SELECT player, killed, COUNT(*) AS kills FROM pvpr_kills WHERE killed = 'Neutronix' GROUP BY player ORDER BY kills DESC LIMIT 1) AS E ON E.killed= A.player

However, if one of these subqueries return null, the entire query fails. I would like to have the null queries instead make those columns null in the returned result set (1 row).

If I changed Neutronix to something not in the database, like dfdsjf, there is an error.

EDIT: Here's the fix I did.

SELECT
    A.player, A.score, B.kills, C.deaths, D.killed AS most_killed, D.kills AS most_killed_count, E.player AS most_killed_by, E.kills AS most_killed_by_count
FROM
(SELECT player, score FROM pvpr_scores WHERE player = 'Gutterknife' AND milestone = 'default') AS A
LEFT OUTER JOIN (SELECT COUNT(*) AS kills FROM pvpr_kills WHERE player = 'Gutterknife' AND milestone = 'default') AS B ON TRUE
LEFT OUTER JOIN (SELECT COUNT(*) AS deaths FROM pvpr_kills WHERE killed = 'Gutterknife' AND milestone = 'default') AS C ON TRUE
LEFT OUTER JOIN (SELECT killed, COUNT(*) AS kills FROM pvpr_kills WHERE player = 'Gutterknife' GROUP BY killed ORDER BY kills DESC LIMIT 1) AS D ON TRUE
LEFT OUTER JOIN (SELECT player, COUNT(*) AS kills FROM pvpr_kills WHERE killed = 'Gutterknife' GROUP BY player ORDER BY kills DESC LIMIT 1) AS E ON TRUE
Improve this question
4
  • You're doing something wrong if it's failing. You're joining all the tables on A. Commented Jul 20, 2013 at 2:05
  • I know I'm doing something wrong @FreshPrinceOfSO. I am not the best at SQL, so I want to know how to do what I want. Commented Jul 20, 2013 at 2:06
  • 2
    It might be better if you post the table structure. as well. and also describe what data you want Commented Jul 20, 2013 at 2:10
  • Is there a reason why you don't want to handle the case where there are no query results in your application code? Commented Jul 20, 2013 at 2:17

2 Answers 2

Reset to default
1

I don't think the problem is the subqueries. I think the problem is the driver query:

FROM (SELECT player, score
      FROM pvpr_scores
      WHERE player = 'Neutronix' AND milestone = 'default'
     ) AS A . . .

If nothing matches this query, then the there are no rows to match in the other queries.

I'm not quite sure how to fix this. I suppose you could do something like this:

FROM (select 'Neutronix' as player) t left outer join
     (SELECT player, score
      FROM pvpr_scores
      WHERE player = 'Neutronix' AND milestone = 'default'
     ) AS A
     on t.player = a.player left outer join . . .

And then change the join conditions in the rest of the from clause to t. instead of a..

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I tried changing all of the joins to ON TRUE and the joins don't work if the query returns a null set. [Err] 1048 - Column 'killed' cannot be null -- This is when the C subquery returns null for the killed column.
@simplyianm . . . Can you reproduce this on SQL Fiddle. I see nothing in the query that says that "'killed'` cannot be null". SQL definitely allows you to left join an empty table to an existing table without generating an error. Are you perhaps inserting this into a table where one of the columns is not null?
I am using a select, so I am not sure why it would be not null. The pvpr_kills table has killed as NOT NULL, though.
Anyways, I've produced a working query that's probably terrible code. Thanks for your help.
0

Why are you running a sub select on the FROM - you could just a straight FROM scores.

You can also have multiple conditions on the 'ON' clause - e.g. ON b.player = a.player and b.milestone = a.milestone.

Rather than sub-selects you can use SUM and IF combined to give you the results - e.g. SUM( IF( row not null, 1, 0)) - this essentially sums 1's and 0's.

Obviously this isn't tested, but I'd approach your query along the lines of:

SELECT scores.player,
        scores.score,
        sum(if(kills.player is not null, 1, 0) as kills,
        sum(if(deaths.player is not null, 1, 0) as deaths
        killed.kills as most_killed,
        killed.killed as most_killed_count,
        killed_by.kills as most_killed_by_count,
        killed_by.killed as most_killed_by
FROM pvpr_scores as scores
LEFT JOIN pvpr_kills as kills
    on kills.player = player
    and kills.milestone = scores.milestone
LEFT JOIN pvpr_deaths as deaths
    on deaths.player = player
    and deaths.milestone = scores.milestone
LEFT JOIN (SELECT killed, player, count(killed) as kills
            FROM kills
            GROUP BY killed, player
            HAVING MAX( count(killed) ) ) as killed
    ON killed.player = scores.player
LEFT JOIN (SELECT killed, player, count(player) as kills
            FROM kills
            GROUP BY player, killed
            HAVING MAX( count(player) ) ) as killed_by
    ON killed_by.killed = scores.player

WHERE scores.player = 'neutronix'
  AND scores.milestone = 'default'

   GROUP BY scores.player
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.