CODEWITHSUNDEEP
mysqlsql
user3253748
user3253748

Reputation: 89

AND statement on same table on values from same field returns 0 results

I'm struggling with the following thing: I have a table called custom_fields. Within it there is a field with values like product_id, money_spent. When I do an AND query to get data I get 0 results (even though the conditions are met). This is the query: 

SELECT DISTINCT  `users` . * 
FROM  `users` 
LEFT JOIN  `emails` ON  `users`.`id` =  `emails`.`user_id` 
LEFT JOIN  `phones` ON  `users`.`id` =  `phones`.`user_id` 
LEFT JOIN  `trackers` ON  `users`.`tracker_id` =  `trackers`.`id` 
LEFT JOIN  `custom_fields` ON  `users`.`id` =  `custom_fields`.`user_id` 
WHERE (
`custom_fields`.`field` =  "product_id"
AND  `custom_fields`.`value` IS NOT NULL 
AND  `custom_fields`.`value` !=  ""
)
AND (
`custom_fields`.`field` =  "payment_value"
)
AND (
`custom_fields`.`value` <50
AND  `custom_fields`.`value` IS NOT NULL 
AND  `custom_fields`.`value` !=  ""
)
AND (
`users`.`tracker_id` =186
)

How to solve this problem? I tried to use UNION but it gives different results. Maybe some aliases? I can't do the transposition on this table (meaning: convert each row to a seperate field)

Upvotes: 0

Views: 55

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

All you seem to want to do is select users for which a product id exists and also a payment value less than 50 exists. So simply use the word EXISTS (that one uses to formulate the task) in your query as well and there are no longer issues with duplicate results. Plus the query is much easier to read, because you use SQL straight-forward.

select *
from users
where tracker_id = 186
and exists
(
  select * 
  from custom_fields
  where field = 'product_id' and value is not null
)
and exists
(
  select * 
  from custom_fields
  where field = 'payment_value' and value < 50 -- and value > 0 maybe?
);

As you see, quite often you can just put the task in words that can be easily translated into SQL.

Please also read in your request's comment section about problems with your query.

Upvotes: 1

Kleskowy
Kleskowy

Reputation: 2668

Supposing that custom_fields is a EAV-style table (i.e. it has multiple entries per one entity like user, in form of a set [entry type, entry label, entry value]), you need to do multiple JOINs with this table to achieve the desired result.

Try this query instead. It filters users having both: entries in custom_fields of type product_id and entries of type payment_value with value lower than 50:

SELECT DISTINCT  `users` . * 
FROM  `users` 
LEFT JOIN  `emails` ON  `users`.`id` =  `emails`.`user_id` 
LEFT JOIN  `phones` ON  `users`.`id` =  `phones`.`user_id` 
LEFT JOIN  `trackers` ON  `users`.`tracker_id` =  `trackers`.`id` 
LEFT JOIN  `custom_fields` as cf1 ON  `users`.`id` =  cf1.`user_id` 
LEFT JOIN  `custom_fields` as cf2 ON  `users`.`id` =  cf2.`user_id` 
WHERE (
cf1.`field` =  "product_id"
AND  cf1.`value` IS NOT NULL 
AND  cf1.`value` !=  ""
)
AND (
cf2.`field` =  "payment_value"
AND cf2.`value` <50
AND  cf2.`value` IS NOT NULL 
AND  cf2.`value` !=  ""
)
AND (
`users`.`tracker_id` =186
)

Upvotes: 1

Prasad
Prasad

Reputation: 56

May be can try using self join / or creating aliases of custom_fields table. Try below query

 SELECT DISTINCT  `users` . * 
    FROM  `users` 
    LEFT JOIN  `emails` ON  `users`.`id` =  `emails`.`user_id` 
    LEFT JOIN  `phones` ON  `users`.`id` =  `phones`.`user_id` 
    LEFT JOIN  `trackers` ON  `users`.`tracker_id` =  `trackers`.`id` 
    LEFT JOIN  `custom_fields` cf1 ON  `users`.`id` =  `cf1`.`user_id` 
    LEFT JOIN  `custom_fields` cf2 ON  `users`.`id` =  `cf2`.`user_id` 

   WHERE (
    `cf1`.`field` =  "product_id"
    AND  `cf1`.`value` IS NOT NULL 
    AND  `cf1`.`value` !=  ""
    )
    AND (
    `cf2`.`field` =  "payment_value"
    )
    AND (
    `cf2`.`value` <50
    AND  `cf2`.`value` IS NOT NULL 
    AND  `cf2`.`value` !=  ""
    )
    AND (
    `users`.`tracker_id` =186
    )

Upvotes: 0

Related Questions