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!
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: