Databases
StackOverflow2013
Postsdbo
POmysql join table on itself
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmysql join table on itself
    text
    copied!<p>I am having problems with this and I'm hoping it's possible.</p> <p>I have a table from wordpress which stores post meta data, so the columns and field data cannot be changed (Easily). </p> <p>The table structure is thus</p> <p>post_id meta_key meta_value</p> <p>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!!</p> <p>So the data would be as follows:</p> <pre><code>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 </code></pre> <p>I've done a GROUP_CONCAT which returns</p> <pre><code>post_id concat 1 10,5,9,matt's post,155 2 51,9,18,James' post,199 </code></pre> <p>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). </p> <p>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....</p> <p>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??</p> <p>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!!</p> <pre><code>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; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload