Complicated(?) UPDATE w/ JOIN

inexion

Hello,

So I'm definitely a beginner, so bear with me. I will try to explain what I'm trying to accomplish. Basically I have an existing table with data that will be operated on to form new records etc...

for example:
id name slat slon elat elon dist
1 65 -10.32 136.64 -10.18 136.69 6556.52
2 65 -10.18 136.69 -10.16 136.72 1701.68
3 65 -10.16 136.72 -10.11 136.72 2106.7
4 66 -10.13 136.73 -10.05 136.79 4536.58
5 66 -10.05 136.79 -10.06 136.82 1082.5
6 66 -10.06 136.82 -10.01 136.84 2238.2

So what I need to do here is basically sum all of the values in the dist column that correspond to any value with the same name value (eg, only 65's), plus take both the slat & slon values from the first distinct record (eg, the 1st 65 slat & slon combined with the last 65 elat & elon values....where 1st would be id#1, last would be id#3 etc.) and combine them with the last elat & elon values...

So from above, my resulting query would give me 2 results looking like so...
id name slat slon elat elon totaldist
1 65 -10.32 136.64 -10.11 136.72 10364.9
2 66 -10.13 136.73 -10.01 136.84 7857.28

any help would be greatly appreciated, thanks!

tave

In this situation, you have two options:

a) Create a somewhat complex MySQL query to do what you want.
b) Get the data from the table(using a simple query) from a php script, manipulate it and put the data back in the database.

I would go with the second one since it is simpler.

inexion

for anyone interested, heres how this query can be written...

mysql> select
g.name,first.slat,first.slon,last.elat,last.elon,g.totaldist
       from ( select min(id) as minid,max(id) as maxid,sum(dist) as
totaldist,name
            from tbl1 group by name ) g
            join tbl1 first on first.id=g.minid
            join tbl1 last on last.id=g.maxid;

tave

I came up with something similar to that. Although yours looks a lot cleaner.

How did you figure it out ?

inexion

it was posted for me by another user of comp.databases.mysql on usenet =]

i am a total mysql beginner so this code is confusing to me, i've been trying to figure it out by looking at the mysql documentation, so any help with stepping through this would be much appreciated. thanks!

tave

First look at the inner SELECT, it is doing two things for you:
1. It sums the distances based on the name. This is done by The SUM(dist) and the GROUP BY name. The GROUP BY combines the records by the name , so that only the record with the same name are used in the SUM() operation.
2. It finds the MAX and MIN id again based in the name. The GROUP BY ensures that it only looks at the the ids of the records with the same name

The "g" is used as an alias of the result set generated by the inner query.

Then the JOIN is used to combine result sets based on some criteria. Think about it as putting two or more tables side by side based on a common column. IN this case is the ids of the names.

Finally look at what the outer select is using : g.name,first.slat,first.slon,last.elat,last.elon,g.totaldist

g.name and g.totaldist are taken from the table "generated in the first query"
first.slat and first.slon are taken from the columns of the first table .
Similarly, last.elat and last.elon are taken from the columns of the last table .

It might help to learn more about how the JOIN works . Take a look at this link, it is not a perfect explanation , but it might help