List:General Discussion« Previous MessageNext Message »
From:Nuno Tavares Date:June 11 2011 4:15am
Subject:Re: How to find values which do not return any tuple in "IN" clause
View as plain text  
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)

Thread
How to find values which do not return any tuple in "IN" clauseFahim Mohammad11 Jun
  • Re: How to find values which do not return any tuple in "IN" clauseFayaz Yusuf Khan11 Jun
    • Re: How to find values which do not return any tuple in "IN" clauseFahim Mohammad11 Jun
  • Re: How to find values which do not return any tuple in "IN" clauseNuno Tavares11 Jun
  • Re: How to find values which do not return any tuple in "IN" clauseClaudio Nanni11 Jun
    • Re: How to find values which do not return any tuple in "IN" clauseClaudio Nanni11 Jun
Re: How to find values which do not return any tuple in "IN" clauseFahim Mohammad11 Jun
  • Re: How to find values which do not return any tuple in "IN" clauseNuno Tavares11 Jun