GROUP BY with aggregate and INNER JOIN

Question

I tried to narrow down the problem as much as possible, it's still something. This is a query that does not work the way I want it:

SELECT *, MAX(tbl_stopover.dist) FROM tbl_stopover INNER JOIN (SELECT edges1.id id1, edges2.id id2, COUNT(edges1.id) numConn FROM tbl_edges edges1 INNER JOIN tbl_edges edges2 ON edges1.nodeB = edges2.nodeA GROUP BY edges1.id HAVING numConn = 1) AS tbl_conn ON tbl_stopover.id_edge = tbl_conn.id1 GROUP BY id_edge 

Here is what I get:

 |id | edge | dist | id1 | id2 | numConn | MAX(tbl_stopover.dist) | ------------------------------------------------------------------ |2 | 23 | 2 | 23 | 35 | 1 | 9 | |4 | 24 | 5 | 24 | 46 | 1 | 9 | ------------------------------------------------------------------ 

and this is what I would like:

 |id | edge | dist | id1 | id2 | numConn | MAX(tbl_stopover.dist) | ------------------------------------------------------------------ |3 | 23 | 9 | 23 | 35 | 1 | 9 | |5 | 24 | 9 | 24 | 46 | 1 | 9 | ------------------------------------------------------------------ 

But let me figure it out a bit ...

I have a graph, let's say this:

  node1 | node2 / \ node3 node4 | | node5 node6 

Therefore, I have a table that I call tbl_edges as follows:

 | id | nodeA | node B | ------------------------ | 12 | 1 | 2 | | 23 | 2 | 3 | | 24 | 2 | 4 | | 35 | 3 | 5 | | 46 | 4 | 6 | ------------------------ 

Now each edge has a " stop_over s" at a certain distance (to nodeA ). So I have a tbl_stopover table like this:

 | id | edge | dist | ------------------------ | 1 | 12 | 5 | | 2 | 23 | 2 | | 3 | 23 | 9 | | 4 | 24 | 5 | | 5 | 24 | 9 | | 6 | 35 | 5 | | 7 | 46 | 5 | ------------------------ 

Why this request?
Suppose I want to calculate the distance between stop_over s. Inside there is one rib that is not a problem. Over the edges is getting harder. But if I have two edges that are connected and there is no other connection, I can also calculate the distance. Here's an example assuming all edges have length of 10 .:

edge 23 has stop_over (id = 3 ) at dist = 9, edge 35 has stop_over (id = 6 ) at dist = 5. Therefore, the distance between these two stop_over is equal to:

 dist = (length - dist_id3) + dist_id5 = (10-9) + 5 

I'm not sure if I made it clear. If this is not clear, do not hesitate to ask a question, and I will do my best to make it more clear.

Answer 1

MySQL allows you to do something stupid - display fields in a summary query that are not part of a GROUP BY or aggregate function like MAX . When you do this, you will get random (as you said) results for the rest of the fields.

In your query, you do this twice - once in your internal query ( id2 not part of GROUP BY or aggregate) and once in the external.

Get ready for random results!

To fix this, try something like this:

 SELECT tbl_stopover.id, tbl_stopover.dist, tbl_conn.id1, tbl_conn.id2, tbl_conn.numConn, MAX(tbl_stopover.dist) FROM tbl_stopover INNER JOIN (SELECT edges1.id id1, edges2.id id2, COUNT(edges1.id) numConn FROM tbl_edges edges1 INNER JOIN tbl_edges edges2 ON edges1.nodeB = edges2.nodeA GROUP BY edges1.id, edges2.id HAVING numConn = 1) AS tbl_conn ON tbl_stopover.id_edge = tbl_conn.id1 GROUP BY tbl_stopover.id, tbl_stopover.dist, tbl_conn.id1, tbl_conn.id2, tbl_conn.numConn 

The main changes are an explicit list of fields (note that I deleted id_edge , since you are joining id1 and already have this field), and adding additional fields to both the internal and external GROUP BY clauses.

If this gives you more rows than you want, you may need to explain more about the desired result set. Something like this is the only way to ensure that you get the appropriate groupings.

Answer 2

Good. This seems to be the answer to my question. I will do another “investigation” because I'm not sure if this is reliable. If someone has something but this, please leave a comment.

 SELECT tbl.id, tbl.dist, tbl.id1, tbl.id2, MAX(dist) maxDist FROM ( SELECT tbl_stopover.id, tbl_stopover.dist, tbl_conn.id1, tbl_conn.id2, tbl_conn.numConn FROM tbl_stopover INNER JOIN (SELECT edges1.id id1, edges2.id id2, COUNT(edges1.id) numConn FROM tbl_edges edges1 INNER JOIN tbl_edges edges2 ON edges1.nodeB = edges2.nodeA GROUP BY edges1.id HAVING numConn = 1) AS tbl_conn ON tbl_stopover.id_edge = tbl_conn.id1 GROUP BY tbl_stopover.dist, tbl_conn.id1 ORDER BY dist DESC) AS tbl GROUP BY tbl.id1, tbl.id2 

Thanks to JNK (my colleague at work), without which I would not have gotten this far.