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 <[email protected]> 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<[email protected]>
>> 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
>>> [email protected]
>>> [email protected]
>>> +91-9746-830-823
>>>
>>>
