Mysql Select COL multiple times for different values

Question

This is the Table Structure:

ID PID KEY VALUE
1 2 CITY NEW YORK
2 2 COUNTRY UNITED STATES
3 2 STATE NEW YORK
4 1 CITY NEW JERSEY

and so on.. what I want is to get/filter the Result like

Where (KEY = CITY and VALUE = NEW YORK) 
      AND (key = country AND VALUE= UNITED STATES)

That's returning empty rows!

There are many Countries and Cities if I search only for the Results which has Country = United States it works perfect I want to get the specific Result Where the Country = United States and City = New York — mahaidery, Jan 13 '12 at 06:17
IF I use Where (KEY = CITY and VALUE = NEW YORK) OR (key = country AND VALUE= UNITED STATES) it works but return ALL United States Data — mahaidery, Jan 13 '12 at 06:20
Why do you have them as different rows instead of different columns? — Sergio Tulentsev, Jan 13 '12 at 06:22

score 1 · Accepted Answer · answered Jan 13 '12 at 06:25

1

You need a self JOIN.

SELECT t1.*
FROM my_table t1
INNER JOIN my_table t2 ON t1.pid = t2.pid
WHERE t1.key = 'city' and t1.value = 'new york'
  AND t2.key = 'country' and t2.value = 'united states';

answered Jan 13 '12 at 06:25

Sergio Tulentsev

226,338
43
373
367

could there be a different method to get these results? I mean assume you're not allowed to make the above query! for example: You're at for each loop foreach($search_terms as $k=>$v){ $term = addslashes_gpc($v); $search .= "{$searchand}(m.value LIKE '{$n}{$term}{$n}' AND m.meta_key LIKE '{$n}{$k}{$n}')"; $searchand = ' AND '; } – mahaidery Jan 13 '12 at 06:42
$search_terms['country']=$_GET['country'];$search_terms['state']=$_GET['state']; – mahaidery Jan 13 '12 at 06:45
@user1147048: sorry, it's freaking hard to read code in the comments. Could you post it pastebin or something like that? – Sergio Tulentsev Jan 13 '12 at 06:47
In this case, you can't do this, because a column can't have two values at the same time. That's why you need a self-join. If you're in control of that meta, you might want to concat values together. Something like: "city=new york&country=united_states". Then you'll be able to test it against of series of LIKE clauses. – Sergio Tulentsev Jan 13 '12 at 06:57
is it possible to write your answer in a foreach loop? – mahaidery Jan 13 '12 at 07:22
If you have access only to `where` clause, you can't. – Sergio Tulentsev Jan 13 '12 at 07:24
what do you think about this query: http://paste2.org/p/1865107 it generate this error: #1241 - Operand should contain 1 column(s) – mahaidery Jan 13 '12 at 07:58
your query looks like this `a = 1 and b and c = 2` where `b` is your subselect. I think, mysql doesn't like that. also, I think you have unbalanced parens there. – Sergio Tulentsev Jan 13 '12 at 08:01

score 0 · Answer 2 · answered Jan 13 '12 at 06:26

So you have a table with different types of values being held in the same column, with a field identifying each set (PID). Is that correct?

If that's so, then you're going to need a self-join here:

SELECT
    DISTINCT cityTable.PID
FROM
    tableName AS cityTable
JOIN
    tableName AS countryTable
    ON cityTable.PID = countryTable.PID
WHERE
    cityTable.key = "city"
    AND
    cityTable.value = "NEW YORK"
    AND
    countryTable.key = "country"
    AND
    countryTable.value = "UNITED STATES";

Mysql Select COL multiple times for different values

2 Answers2

Linked