CODEWITHSUNDEEP
mysqlsql
Игорь Зуев
Игорь Зуев

Reputation: 19

SQL AND Operator Not Working VERTICALLY

I have a very simple MySQL table:

look at the screenshot

My SQL query is as follows:

SELECT rpcf.place_id
FROM rel_place_custom_fields rpcf 
WHERE 
(
  (rpcf.field_id = 21 AND rpcf.field_value = 'Italy') 
  AND 
  (rpcf.field_id = 22 AND rpcf.field_value = 'XL')
)

This query returns nothing, though 10035 place_id item meets both conditions. Looks like the problem is that the data is in two different rows. How can I rewrite the query to get places_ids that have both 21/Italy and 22/XL?

Upvotes: 0

Views: 43

Answers (3)

Sujay Gavhane
Sujay Gavhane

Reputation: 169

First you need to use OR in between two different where conditions like this.

SELECT 
    DISTINCT rpcf.place_id
FROM 
    rel_place_custom_fields rpcf 
WHERE 
(
  (rpcf.field_id = 21 AND rpcf.field_value = 'Italy') 
  OR 
  (rpcf.field_id = 22 AND rpcf.field_value = 'XL')
)

also use DISTINCT to return uniq values of rpcf.place_id.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

I'd go with M Khalid Junaid's answer. The alternative would be to look up the conditions one by one:

select *
from places
where place_id in 
(
 select place_id from rel_place_custom_fields where field_id = 21 and field_value = 'Italy'
)
and place_id in 
(
 select place_id from rel_place_custom_fields where field_id = 22 and field_value = 'XL'
);

The same can be done with EXISTS clauses of course.

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can rewrite your query as below to get place_id who has all these attributes not only single attribute

SELECT 
  rpcf.place_id 
FROM
  rel_place_custom_fields rpcf 
GROUP BY rpcf.place_id 
HAVING SUM(rpcf.field_id = 21 AND rpcf.field_value = 'Italy') 
AND SUM(rpcf.field_id = 22 AND rpcf.field_value = 'XL')

Upvotes: 1

Related Questions