How to find values which do not return any tuple in IN clause
Hi
select * from tablename where fieldname in ('aaa','bbb','ccc','ddd');
return only the successful hit.
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.
Thanks
Fahim
Re: How to find values which do not return any tuple in IN clause
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.k...@gmail.com
fa...@dexetra.com
+91-9746-830-823
signature.asc
Description: This is a digitally signed message part.
Re: How to find values which do not return any tuple in IN clause
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 fa...@dexetra.com 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.k...@gmail.com
fa...@dexetra.com
+91-9746-830-823
Re: How to find values which do not return any tuple in IN clause
create table dim (value varchar(10), primary key(value));
insert into dim values ('aaa'),('bbb'),('ccc'),('ddd');
SELECT tablename.fieldname
FROM dim
LEFT JOIN tablename ON tablename.fieldname = dim
WHERE tablename.fieldname IS NULL;
If this is not what intended, I'd suggest you to spend a little more
effort in formulating a more clear question, maybe with an example.
-NT
Em 10-06-2011 23:36, Fahim Mohammad escreveu:
Hi
select * from tablename where fieldname in ('aaa','bbb','ccc','ddd');
return only the successful hit.
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.
Thanks
Fahim
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to find values which do not return any tuple in IN clause
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', '','y') GROUP BY state_id;
+-+--+
| city_name | COUNT(*) |
+-+--+
| Clarksville |2 |
| Nashville |1 |
| Ashburn |1 |
+-+--+
3 rows in set (0.00 sec)
Counts for '', 'y' do not appear here.
mysql SELECT city_name, COUNT(*) FROM city WHERE city_name IN
('','y') 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 rhi...@sympatico.ca 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(*)
---
aaa3
bbb 0
ccc0
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 Khanfa...@dexetra.com
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.k...@gmail.com
fa...@dexetra.com
+91-9746-830-823
Re: How to find values which do not return any tuple in IN clause
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 ('','y') 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', '','y') 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
('','y') GROUP BY state_id having count(*) = 0;
Empty set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
