Self Join SQL Query

Question

a few days ago I asked for a solution to a SQL query i needed to work out, my data looks like:

meta_id post_id     meta_key    meta_value
269     4   _apais               USA
270     4   _aciudad             New york
271     4   _aservicio           Pleasure

...
272     43  _apais               USA
273     43  _aciudad             Chicago
274     43  _aservicio           Fun
...
275     44  _apais               USA
276     44  _aciudad             Miami
277     44  _aservicio           Night Life

278     45  _apais               USA
279     45  _aciudad             Miami
280     45  _aservicio           Sports

What i need to do, is to display all the registers that matches Country with City and Service . Or order all the _aciudad registers by country (and _aservicio As service), something like:

meta_id     post_id     meta_key    meta_value  meta_key    meta_value  meta_key  meta_value
270         7           _apais          USA     _aciudad    New York   _aservicio Pleasure
261         13          _apais          USA     _aciudad    Chicago    _aservicio Fun
276         4           _apais          USA     _aciudad    Miami      _aservicio Sports

@Ravi Gummadi Gave me a solution that looks like this:

SELECT t1.meta_id, 
           t1.post_id, 
           t1.meta_key, 
           t1.meta_value, 
           t2.meta_key, 
           t2.meta_value 
    FROM th_postmeta t1, th_postmeta t2  
    WHERE t1.post_id = t2.post_id 
    AND t1.meta_key = '_apais'
    AND t2.meta_key = '_aciudad'
    ORDER BY t1.meta_key

That query returns this:

meta_id     post_id     meta_key    meta_value  meta_key    meta_value
270         7           _apais          USA     _aciudad    New York
261         13          _apais          USA     _aciudad    Chicago
276         4           _apais          USA     _aciudad    Miami
279         10          _apais          USA     _aciudad    Miami

How can I acchive to show only the records that are not duplicated in the meta_value value (for _aciudad and _aservicio only, _apais can be duplicated)?

Thanks a lot guys!

Which row would you like to keep?, what is the criteria for this?. On your example, I could choose `meta_id` 276 or 279. — Lamak, Mar 31 '11 at 17:13
I need to show _apais, _aservicio, _aciudad and _aciudad must be DISTINCT (his value can't be repeated) — Alberto, Mar 31 '11 at 17:19
yes, I understood that. But when `a_ciudad` **is** duplicated, you need to choose one record. What is the criteria for this?, since they will be on different `post_id` — Lamak, Mar 31 '11 at 17:23
@Alberto - Your comment makes no sense to me. _apais, _aservicio, and _aciudad are data values, not the names of your columns. Out of this list - meta_id, post_id, meta_key, meta_value, meta_key, meta_value - which values do you need? Perhaps give is an example of what the output *should* look like? — Joshua Carmody, Mar 31 '11 at 17:24
@Lamak, For each post_id would never be more than one _aciudad, _aservicio, _apais. And there's no criteria really, this is just for generating a list of the _aservicios that are availiable on the db. Any criteria would work i guess, as it don't repeat the listed values. @Joshua Carmody there's an expable in my original comment of how should the output look, thanks to both of you! — Alberto, Mar 31 '11 at 19:09
@Alberto - can you post your expected results? you show us what the query IS returning to you....how would you LIKE it to be? — Leslie, Mar 31 '11 at 19:19

Joshua Carmody · Answer 1 · 2011-03-31T19:37:28.840

If you don't need the meta_id and post_id, you could just delete the text t1.post_id, and t1.meta_id, and then change SELECT to SELECT DISTINCT.

If you do need the post_id, you'll need to tell us how to decide which row to keep - either meta_id=276 or meta_id=279 - as Lamak noted in his comment above.

Edit 1:

If you want to keep meta_id and post_id in the query, but you don't care about which values from meta_id and post_id get kept, then you can do this:

SELECT
    MAX(t1.meta_id), 
    MAX(t1.post_id), 
    t1.meta_key, 
    t1.meta_value, 
    t2.meta_key, 
    t2.meta_value 
FROM th_postmeta t1, th_postmeta t2  
WHERE t1.post_id = t2.post_id 
AND t1.meta_key = '_apais'
AND t2.meta_key = '_aciudad'
GROUP BY t1.meta_key, t1.meta_value, t2.meta_key, t2.meta_value
ORDER BY t1.meta_key

Note that it's possible that you'll get meta_ids and post_ids from different records "blended" together using this method, since it's not guaranteed that the record with the highest meta_id will also have the highest post_id, and vice-verse.

If you don't need the meta_id or post_id and just want the meta_keys and meta_values, you can do this:

SELECT DISTINCT
    t1.meta_key, 
    t1.meta_value, 
    t2.meta_key, 
    t2.meta_value 
FROM th_postmeta t1, th_postmeta t2  
WHERE t1.post_id = t2.post_id 
AND t1.meta_key = '_apais'
AND t2.meta_key = '_aciudad'
ORDER BY t1.meta_key

If neither of these methods work for you, then you need to give us some additional criteria so we know how to refine the solution for you.

like this?: SELECT DISTINCT t1.meta_id, t1.meta_key, t1.meta_value, t2.meta_key, t2.meta_value FROM th_postmeta t1, th_postmeta t2 WHERE t1.post_id = t2.post_id AND t1.meta_key = '_apais' AND t2.meta_key = '_aciudad' ORDER BY t1.meta_key It still show duplicated rows for the t2.meta_value records :-| — Alberto, Mar 31 '11 at 19:20
@Alberto - Ah, right. I forgot about meta_id. You'd need to get rid of that from your select too. — Joshua Carmody, Mar 31 '11 at 19:28
Joshua, I solved it. It was simpler: SELECT DISTINCT t1.meta_id, t1.meta_key, t1.meta_value, t2.meta_key, t2.meta_value FROM th_postmeta t1, th_postmeta t2 WHERE t1.post_id = t2.post_id AND t1.meta_key = '_apais' AND t2.meta_key = '_aciudad' GROUP BY t2.meta_value It worked as a charm, thanks so much! — Alberto, Mar 31 '11 at 19:31
Thanks a lot Joshua! I used your solution to do a a self join with three fields and worked great! — Alberto, Mar 31 '11 at 20:28

score 1 · Accepted Answer · edited May 23 '17 at 12:04

So you will end up with more than 1 row with the same meta_value. You want to discard some of these rows, even though they have different values for other fields? In SQL Server you might do something like:

SELECT
    ..., x = ROW_NUMBER() OVER(PARTITION BY meta_value ORDER BY meta_id )
WHERE
   x <= 1

This introduces another calculated column (x) with an increasing number for each row (order by) restarting on each meta_value (partition by). The order by clause defines which ones are most important to keep/discard and the where is how many of each you keep.

meta_id     post_id     meta_key    meta_value  meta_key    meta_value   x
261         13          _apais          USA     _aciudad    Chicago      1
270         7           _apais          USA     _aciudad    New York     2
276         4           _apais          USA     _aciudad    Miami        3
279         10          _apais          USA     _aciudad    Miami        4
280         10          _apais          ABC     _aciudad    Miami        1
281         10          _apais          ABC     _aciudad    Miami        2

I realize you tagged it MySql. I don't really use MySQL and hope this just gives you a clue for something new to Google. Perhaps:

ROW_NUMBER() in MySQL

Self Join SQL Query

2 Answers2