mysql join table on itself

Question

I am having problems with this and I'm hoping it's possible.

I have a table from wordpress which stores post meta data, so the columns and field data cannot be changed (Easily).

The table structure is thus

post_id meta_key meta_value

the meta key stores a field name and the meta_value, the value for that field. I need to group these based on the post ID so I can then do a compare between two of the fields. I've tried all sorts!!

So the data would be as follows:

post_id   meta_key        meta_value
1         _wp_field0      10
1         _wp_field1      5
1         _wp_field2      9
1         _wp_field3      "matt's post"
1         _wp_field3      155
2         _wp_field0      51
2         _wp_field1      9
2         _wp_field2      18
2         _wp_field3      "james' post"
2         _wp_field3      199

I've done a GROUP_CONCAT which returns

post_id     concat
1           10,5,9,matt's post,155
2           51,9,18,James' post,199

that is the closest I've come to getting what I want. But I am not sure how to then split that up. I have a whole series of ints which I want to compare (fields1 and 2).

I need the second and third numbers from the string above. Can I do something in SQL to get these? I then want to do some maths on it. They are basically floats which store longitude and latitude which I want to compare and get distance....

I was thinking of having a temp table? All I need is to reference those two numbers. Maybe I can split the string up by separator??

I've run out of ideas. (ps) I've also tried to write my own function (as below) but this crashed the server several times!!

DROP FUNCTION get_lat;
DELIMITER $$
CREATE FUNCTION get_lat(in_post_id INT)
RETURNS FLOAT
READS SQL DATA
BEGIN
DECLARE latitude FLOAT;
SELECT meta_value INTO latitude FROM wp_postmeta WHERE post_id = in_post_id AND meta_key = 'field1';
RETURN (latitude);
END $$
DELIMITER;

DROP FUNCTION get_lon;
DELIMITER $$
CREATE FUNCTION get_lon(in_post_id INT)
RETURNS FLOAT
READS SQL DATA
BEGIN
DECLARE longitude FLOAT;
SELECT meta_value INTO longitude FROM wp_postmeta WHERE post_id = in_post_id AND meta_key = 'field2';
RETURN (longitude);
END $$
DELIMITER;

SELECT post_id,get_lat(post_id)as latitude, get_lon(post_id) as longitude FROM wp_postmeta GROUP BY post_id;

Answer 1

To join a table to itself you need to give the table an alias (as you would with a field) and then select from both the table and it's alias.

E.g.

SELECT table1.foo, table2.bar FROM table as table1, table as table2 WHERE table1.foo = table2.bar

This article gives a better example of how to use this syntax.

Answer 2

select t1.post_id, t1.meta_value as lat, t2.meta_value as lon
from metatdatatable t1, metadatatable t2
where t1.meta_key = "_wp_field1"
and t2.post_id = t1.post_id
and t2.meta_key = "_wp_field2"

Edit

...to use this as the basis for yr lat/long calc, you can either create a temp table, or use the results inline with a query similar to the (simplified DISTANCE calc) below

select d1.post_id, d1.distance
from
(select r1.post_id, ABS($lat - r1.lat) + ABS($lon - r1.lon) as DISTANCE
from (select t1.post_id, t1.meta_value as lat, t2.meta_value as lon
    from metatdatatable t1, metadatatable t2
    where t1.meta_key = "_wp_field1"
    and t2.post_id = t1.post_id
    and t2.meta_key = "_wp_field2") as r1
) as d1
where d1.distance <= 10
order by d1.distance ASC

NB. you might want to apply a rough filter to your lat/long results before doing the 'expensive' lat/long calculation on the results, or before storing in a temp table. The idea would be to ignore all of the r1 results clearly outside of a 10 mile radius.

If you use a temp table it will be user session specific.

Edit 2

Have a look at Shape of the Earth for details, but essentially 7 minutes lat & longitude is always greater than 10 miles, so if your lat & longs are recorded in degrees this is 0.117 near enough. Any point differing by more than 0.117 from your target cannot be inside your 10 mile radius. This means you can filter the r1 table like :

(select t1.post_id, t1.meta_value as lat, t2.meta_value as lon
        from metatdatatable t1, metadatatable t2
        where t1.meta_key = "_wp_field1"
        and t2.post_id = t1.post_id
        and t2.meta_key = "_wp_field2"
        and ABS(t2.meta_value - $lon) < 0.117
        and ABS(t1.meta_value - $lat) < 0.117
) as r1  

NB. If your data spans the Greenwich Meridian, International Date Line or Equator, this will not be strictly correct. Assuming all of your lat/longs are for North America it won't be a problem.

Answer 3

Ehm, surely this is:

SELECT post_id, long.meta_value, lat.meta_value
FROM wp_postmeta long
INNER JOIN wp_postmeta lat 
  ON long.post_id = lat.post_id
WHERE long.meta_key = '_wp_field1'
AND lat.meta_key = '_wp_field2'