CODEWITHSUNDEEP
mysqlsqljoin
Ian Macalinao
Ian Macalinao

Reputation: 1668

MySQL join with null values

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

Upvotes: 0

Views: 5142

Answers (2)

stefancarlton
stefancarlton

Reputation: 1727

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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..

Upvotes: 1

Related Questions