From: Nuno Tavares
Date: June 11 2011 4:15am
Subject: Re: How to find values which do not return any tuple in "IN" clause
List-Archive: http://lists.mysql.com/mysql/225225
Message-Id: <4DF2EBD2.4000101@dri.pt>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Fahim,

I was not quite sure I understood you question, but looking at what
you're trying to achieve in this example, give a try to my suggestion.


You have to bear in mind that you can only GROUP BY values that indeed
show up in the results. That means that if ('xxxx','yyyyy') never show
up, they won't show up in the GROUP BY either.

That's why I suggested to use a "value table" (dim in my example) -
there you will load all the values you want, so you can show then in the
results by means of a LEFT JOIN.


Also, watch out for this:
mysql> SELECT city_name, COUNT(*) FROM city  WHERE city_name  IN
('Nashville','Ashburn','Clarksville', 'xxxx','yyyyy') GROUP BY state_id;


You're grouping by state_id but showing city_name (which you can't
control)... that's dangerous unless you are pretty sure of what you're
doing.

-NT


Em 11-06-2011 01:38, Fahim Mohammad escreveu:
> mysql> SELECT city_name, COUNT(*) FROM city  WHERE city_name  IN
> ('xxxx','yyyyy') GROUP BY state_id having count(*) = 0;
> Empty set (0.00 sec)