--20cf3024505f4938a504a5633322
Content-Type: text/plain; charset=ISO-8859-1
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
--20cf3024505f4938a504a5633322--
--nextPart2325063.XpcBnisA1o
Content-Type: Text/Plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
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=20
('aaa','bbb','ccc','ddd') GROUP BY fieldname;"
=2D-=20
=46ayaz 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@gmail.com
fayaz@dexetra.com
+91-9746-830-823
--nextPart2325063.XpcBnisA1o
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
iJwEAAECAAYFAk3ypgUACgkQRrmH0/kbu8//RQP+KTsdKrC0DExI2vxT8Oyn x6XK
U024y+dfkTK/Ph3Re1o6O3vsRbW+a4d7KajpTBJw/uG/oZG5TQRZvxI0OiWq QChz
j62EtF4Dnt2tv/7/XYzeqCWUVuaGPCTNmexg6rhoVSgug6FKTMQGkbTjT6Ii w0K2
21MG68ZMsTyE5yYQseo=
=3PWw
-----END PGP SIGNATURE-----
--nextPart2325063.XpcBnisA1o--
--001517476246b123ac04a563eb8d
Content-Type: text/plain; charset=ISO-8859-1
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
> 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@gmail.com
> fayaz@dexetra.com
> +91-9746-830-823
>
--001517476246b123ac04a563eb8d--
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=gcdmg-mysql-2@m.gmane.org
--0016e6d96c6c1c8c3604a564e973
Content-Type: text/plain; charset=ISO-8859-1
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
>
> 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
>> 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@gmail.com
>>> fayaz@dexetra.com
>>> +91-9746-830-823
>>>
>>>
--0016e6d96c6c1c8c3604a564e973--
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)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
--90e6ba6e8d90af0a8d04a56b2f0a
Content-Type: text/plain; charset=ISO-8859-1
Hi Fahim,
Could you please explain better what you mean exactly?
I think the question is not very clear.
Thanks
Claudio
On Jun 11, 2011 12:36 AM, "Fahim Mohammad"
> 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
--90e6ba6e8d90af0a8d04a56b2f0a--
--90e6ba6e8438c5be2c04a56c7dc3
Content-Type: text/plain; charset=ISO-8859-1
Sorry but my mail was in "sending state" since last night, and only now was
sent.
Ignore it.
Claudio
2011/6/11 Claudio Nanni
> Hi Fahim,
>
> Could you please explain better what you mean exactly?
> I think the question is not very clear.
> Thanks
>
> Claudio
> On Jun 11, 2011 12:36 AM, "Fahim Mohammad"
> > 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
>
--
Claudio
--90e6ba6e8438c5be2c04a56c7dc3--