List:General Discussion« Previous MessageNext Message »
From:Fahim Mohammad Date:June 11 2011 12:38am
Subject:Re: How to find values which do not return any tuple in "IN" clause
View as plain text  
Thank you all for prompt reply.
I think there is no short cut and I may need to write nested query for doing
this.
The method suggested by you is not working.

mysql> select * from city;
+---------+-------------      +----------+
| city_id | city_name    | state_id |
+---------+-------------      +----------+
|       1 | Melbourne    |        1 |
|       2 | Clarksville    |        2 |
|       3 | Nashville     |        3 |
|       4 | Ashburn       |        4 |
|       5 | Las Vegas   |        5 |
|       6 | Hermitage    |        6 |
|       7 | Nashville     |        2 |
+---------+-------------+----------+
7 rows in set (0.00 sec)


mysql> SELECT city_name, COUNT(*) FROM city  WHERE city_name  IN
('Nashville','Ashburn','Clarksville', 'xxxx','yyyyy') GROUP BY state_id;
+-------------+----------+
| city_name   | COUNT(*) |
+-------------+----------+
| Clarksville |        2 |
| Nashville   |        1 |
| Ashburn     |        1 |
+-------------+----------+
3 rows in set (0.00 sec)

Counts for 'xxxx', 'yyyyy' do not appear here.

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)

This does not work either.

I think I will have to go for nested query.
Thanks again for reply
--- Fahim


On Fri, Jun 10, 2011 at 7:48 PM, Rhino <rhino1@stripped> wrote:

>
>  The simplest approach is to simply do a count(*) query. This would involve
> doing one simple query for each value of "fieldname" that you were scanning
> for missing tuples. For example:
>
> Select count(*) from tablename where fieldname is 'aaa'
>
> Just do that same query for each of the values you care replacing 'aaa'
> with 'bbb', then 'ccc', then 'ddd'. Each time you get a value of 0, you know
> that the fieldname value ('aaa', 'bbb', or whatever) doesn't exist in the
> table.
>
> --
>
> The query which was suggested to you - select fieldname, count(*) from
> tablename where fieldname in ('aaa', 'bbb','ccc','ddd') group by fieldname -
> will report on the number of rows containing each of the specified values,
> showing 0 when that value doesn't occur at all. Therefore, you might get
> something like this:
>
> fieldname  count(*)
> -------- -------
> aaa                        3
> bbb                       0
> ccc                        0
> ddd                       9
>
> That answers your question (as I understand it) but gives a little more
> information than you actually wanted since it shows 'aaa' and 'ddd' which DO
> have tuples. If you want to see only 'bbb' and 'ccc' which have no tuples,
> modify the query by adding this having clause:
>
> select fieldname, count(*) from tablename
>
> where fieldname in ('aaa', 'bbb','ccc','ddd')
> group by fieldname
> having count(*) = 0
>
> I haven't tested that but it SHOULD work assuming you are using a
> reasonably recent version of MySQL.
> --
> Rhino
>
>
> On 2011-06-10 19:28, Fahim Mohammad wrote:
>
>> I am looking for those values (or count of those values) which do not
>> resulted in a hit.
>> Thanks
>> Fahim
>>
>> On Fri, Jun 10, 2011 at 7:17 PM, Fayaz Yusuf Khan<fayaz@stripped>
>>  wrote:
>>
>>  On Saturday 11 Jun 2011 4:06:27 AM Fahim Mohammad wrote:
>>>
>>>> select * from  tablename where fieldname in ('aaa','bbb','ccc','ddd');
>>>> How can I know how many out of four ('aaa','bbb','ccc','ddd') resulted
>>>> in
>>>>
>>> a
>>>
>>>> miss OR which values do not return any tuple.
>>>>
>>> Perhaps this  would be what you're looking for?
>>> "SELECT fieldname,COUNT(*) FROM tablename WHERE fieldname IN
>>> ('aaa','bbb','ccc','ddd') GROUP BY fieldname;"
>>>
>>> --
>>> Fayaz Yusuf Khan
>>> Cloud developer and designer in Python/AppEngine platform
>>> Dexetra Software Solutions Pvt. Ltd., Kochi, Kerala, India
>>> B.Tech. Computer Science&  Engineering (2007-2011)
>>> Model Engineering College, Kochi, Kerala, India
>>> Registered Linux user #484201
>>> fayaz.yusuf.khan@stripped
>>> fayaz@stripped
>>> +91-9746-830-823
>>>
>>>

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