Specific SQL query (multiple meta_value / same row)

Question

I can't make a proper SQL query. I need to query my Wordpress DB, with select multiple meta_value from specifics meta_key, and have the results on a same row.

The table :

table

I just want to have the meta_value from meta_key = '1', 'gvmaps_lat_2' and 'gvmaps_long_2' on the same row, but I can't find how.

If I make this query, the results are good, but not on the same row :

SELECT e.id, e.date_created, m.meta_value
FROM tp_gf_entry AS e, tp_gf_entry_meta AS m
WHERE e.id = m.entry_id
AND m.meta_key IN ('1', 'gvmaps_lat_2', 'gvmaps_long_2')

If I make this one, I have an error :

SELECT e.id, e.date_created, (SELECT meta_value FROM tp_gf_entry_meta WHERE meta_key = '1')
FROM tp_gf_entry AS e, tp_gf_entry_meta AS m
WHERE e.id = m.entry_id
AND m.meta_key IN ('1', 'gvmaps_lat_2', 'gvmaps_long_2')

1242 - Subquery returns more than 1 row

Howard E · Accepted Answer · 2020-04-08 10:29:58Z

1

I'm unable to run your query since I don't have all of your fields... but what it looks like you're after is a JOIN so that your output will all be in one row. Untested, but should work.

SELECT DISTINCT
    e.id,
    e.date_created,
    m.meta_value as title,
    m2.meta_value as lat,
    m3.meta_value as lon
FROM
    tp_gf_entry e
LEFT JOIN tp_gf_entry_meta  m ON e.ID = m.entry_id AND m.meta_key = '1'
LEFT JOIN tp_gf_entry_meta m2 ON e.ID = m2.entry_id AND m2.meta_key LIKE 'gvmaps_lat_2' 
LEFT JOIN tp_gf_entry_meta m3 ON e.ID = m3.entry_id AND m3.meta_key LIKE 'gvmaps_long_2' 
WHERE
    e.id = m.entry_id

answered Apr 8, 2020 at 10:29

Howard E

5,6893 gold badges17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Over a year ago

That is the solution ! A big thank for your Help @Howard

Collectives™ on Stack Overflow

Specific SQL query (multiple meta_value / same row)

1242 - Subquery returns more than 1 row

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1242 - Subquery returns more than 1 row

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related