Two "where" statements with one table doesn't work without subqueries

Question

I've got a table with 3 columns: name, lastname and date.

For example:

Name Lastname Date
Ab   Ab       2008-07-01
Ab   Ab       2006-06-23
Kb   Kb       2008-07-01
Kb   Kb       2007-06-03

I need to find the names of those who are assigned to 2008-07 and not to 2006-06. So for this example the output will be:

Name Lastname Date
Kb   Kb       2008-07-01
Kb   Kb       2007-06-03

My code:

select  Name, Lastname, YEAR(date), MONTH(date) from MyTable
where (YEAR(date) = 2008 AND MONTH(date) = 7) AND (YEAR(date) <> 2006 AND MONTH(date) <> 6)

It doesn't work well, because really nothing happen (it "blocking" for example all names with month = 6). I try to do it with UNION statement, but nothing work well.

Note, I need to do this without using a subquery.

@mucio I'm learning for exam and it's my task to do something like that. — Cassy_1, Feb 04 '15 at 00:43

score 5 · Accepted Answer · edited Feb 04 '15 at 01:02

5

I think you want a group by and a having clause, because you are looking for multiple rows:

select  Name, Lastname
from MyTable
group by Name, LastName
having 
    sum(case when YEAR(date) = 2008 AND MONTH(date) = 7 then 1 else 0 end) > 0 and
    sum(case when YEAR(date) = 2006 AND MONTH(date) = 6 then 1 else 0 end) = 0;

Each condition in the having clause counts the number of rows that match. The > 0 means there is at least one. The = 0 means there are none. This generalizes easily to more conditions.

edited Feb 04 '15 at 01:02

Felix Pamittan

31,544
7
41
67

answered Feb 04 '15 at 00:24

Gordon Linoff

1,242,037
58
646
786

Hi! Thanks for your post, but it doesn't works fine for me. I want only those names who are for example assigned to date "2008.07" BUT NOT to "2006.06". This solution gives me even those names, which are for example assigned to "2006.06" but without printing this date. – Cassy_1 Feb 04 '15 at 00:36
@Cassy_1 . . . It should not give you people assigned to 2006-06, because the second condition excludes them. – Gordon Linoff Feb 04 '15 at 00:50
Ok. You're right. I just do it one more time, and there is a good result. Thanks! – Cassy_1 Feb 04 '15 at 01:00
@GordonLinoff, I accidentally downvoted your answer. Edited it so it'll be unlocked for voting. – Felix Pamittan Feb 04 '15 at 01:03

Felix Pamittan · Answer 2 · 2015-02-04T00:47:40.830

1

SELECT
    t.*
FROM (
    SELECT
        Name,
        LastName
    FROM #Temp
    GROUP BY Name, LastName
    HAVING
        SUM(CASE WHEN YEAR([Date]) = 2008 AND MONTH([Date]) = 7 THEN 1 ELSE 0 END) = 1
        AND SUM(CASE WHEN YEAR([Date]) = 2006 AND MONTH([Date]) = 6 THEN 1 ELSE 0 END) = 0
)x
INNER JOIN #Temp t
    ON t.Name = x.Name
    AND t.LastName = x.LastName

RESULT

Name                 LastName             Date
-------------------- -------------------- ----------
Kb                   Kb                   2008-07-01
Kb                   Kb                   2007-06-03

edited Feb 04 '15 at 00:47

answered Feb 04 '15 at 00:26

Felix Pamittan

31,544
7
41
67

Hi! Thanks for your post, but it doesn't works fine for me. I want only those names who are for example assigned to date "2008.07" BUT NOT to "2006.06". – Cassy_1 Feb 04 '15 at 00:35
It still does not work like I wanted to. Lets say that the names are names of customers. I want to write out only those who ever made the request in 07.2008 but not in 06.2006. And it also gives me those who have request in 06.2006 but simply without writing that date. – Cassy_1 Feb 04 '15 at 00:42
Isn't this using a subquery? – Aaron D Feb 04 '15 at 01:15

score 1 · Answer 3 · answered Feb 04 '15 at 00:50

You can use a sequence of outer joins to do it with no subquery.

SELECT c1.*
FROM Customers c1
LEFT OUTER JOIN Customers c2 ON
    c1.Name = c2.Name AND c1.LastName = c2.LastName
    AND YEAR(c2.date) = 2006 AND MONTH(c2.date) = 6
LEFT OUTER JOIN Customers c3 ON
    c1.Name = c3.Name AND c1.LastName = c3.LastName
    AND YEAR(c3.date) = 2008 AND MONTH(c3.date) = 7
WHERE 
    c2.date IS NULL
    AND c3.date IS NOT NULL

You're asserting that the first join fails, but the second join succeeds.

The results are

Kb Kb 7/1/2008 12:00:00 AM 
Kb Kb 6/3/2007 12:00:00 AM

Two "where" statements with one table doesn't work without subqueries

3 Answers3