current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have db table like this:

id | user | service |   pay_date   |   status

---+------+---------+--------------+------------

1  | john |    1    |  2014-08-20  |     1    
2  | john |    3    |  2014-07-24  |     0

I am trying to build query which return all users who are not payed service 1 or service 3 between 2 dates and haven`t active services (status = 0 for service 1 and service 2). The problem is that query return service 3, but this is not correct because service 1 is active.

SELECT * 
FROM services 
WHERE date > 2014-07-01 
  AND date < 2014-07-30 
  AND service = 1 OR service = 3

My solution is to check if service 3 is active when i`am checking service 1. Something like that...

SELECT * 
FROM services 
WHERE date > 2014-07-01 
  AND date < 2014-07-30 
  AND ((service = 1 AND "SERVICE 3 IS NOT ACTIVE") OR service = 3)

but i cant check if "SERVICE 3 IS NOT ACTIVE" for current user

share|improve this question
    
The problem is in the logic, this queries are just for example. This query return service 3 but service 1 is active. I want to get only users who haven`t active services now but they has active service in this period –  Ивайло Димитров Aug 21 '14 at 12:32
    
two words: self join –  joop Aug 21 '14 at 12:32
    
try with status = 0 where service = 3 at the place of "SERVICE 3 IS NOT ACTIVE" –  Himanshu Aug 21 '14 at 12:33
    
Can you clarify your question? After reading a couple of times it is still not clear for me. –  Erwin Brandstetter Aug 21 '14 at 13:35

2 Answers 2

up vote 0 down vote accepted

I think you want NOT EXISTS, i.e. checking that another active record does not exist for the same user, possibly something like:

SELECT *
FROM    Services
WHERE   Service IN (1, 3)
AND     pay_date > '2014-07-01'
AND     pay_date < '2014-07-30'
AND     NOT EXISTS 
        (   SELECT  1 
            FROM    services AS s2 
            WHERE   s2.Name = s.Name 
            AND     s2.Status = 1 
            AND     s2.Service IN (1, 3)
            AND     s2.pay_date > '2014-07-01'
            AND     s2.pay_date < '2014-07-30'
        );
share|improve this answer
up vote 0 down vote

There is no single record that meets all those criteria.

id 1 record cannot be both service = 1 & service = 3 at the some time

id | user | service |   pay_date   |   status
---+------+---------+--------------+------------
1  | john |    1    |  2014-08-20  |     1

id 2 record cannot be both service = 1 & service = 3 at the some time

id | user | service |   pay_date   |   status
---+------+---------+--------------+------------
2  | john |    3    |  2014-08-24  |     0       -- nb: I changed this date!

SO: your criteria require comparing record 1 to record 2 (or more broadly, that you have to compare "across records"). below I use EXISTS to compare across records.

SELECT
      *
FROM services
WHERE (pay_date >= '2014-08-01'
      AND pay_date < '2014-08-30')
      AND service = 1
      AND EXISTS (
            SELECT
                  NULL
            FROM services s3
            WHERE (s3.pay_date >= '2014-08-01'
                  AND s3.pay_date < '2014-08-30')
                  AND s3.service = 3
                  and s3.status = 1
                  AND s3.user = services.user
                 )

see SQLfiddle Demo

by the way, it is more conventional to use a date range via >= & <

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.