[Solved]Query to find within Group

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

waqas_punjabian
Forum Commoner
Posts: 67
Joined: Wed Aug 10, 2005 9:53 am

Post by waqas_punjabian »

Thanks Volka,

So, now i have only one index on this table that is on ProductID.

And thanks mikeq,
For all of your posts and your optimized query,

it really express better results. like: 0.0011 second.

Thanks to all of you for this great and informative discussion.

Regards,

Waqas
waqas_punjabian
Forum Commoner
Posts: 67
Joined: Wed Aug 10, 2005 9:53 am

Post by waqas_punjabian »

Can you please tell me, from where i can download this 'Webinar'

Any related link ?
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

http://www.mysql.com/news-and-events/on ... -webinars/

You need to register, which is free to do. You will also need to download the player software, just read the instructions on the site.

Mike
waqas_punjabian
Forum Commoner
Posts: 67
Joined: Wed Aug 10, 2005 9:53 am

Post by waqas_punjabian »

Thanks Mike,
Here i need another Query, If you help me i will be really thanking to you.


I need a query which will select those records/groups which have PrimaryFlag='y' in more than one records, for example look at ProductID 2 group.

Code: Select all

tbl_images
------------------------------------------------------------
imgID	ProductID	ImageFile	PrimaryFlag
------------------------------------------------------------
1		    1		     a			   n
2		    1		     b			   n
3		    1		     c			   y

4		    2		     d			   n
5		    2		     e			   y
6		    2		     f			   y

7		    3		     g			   y
8		    3		     h			   n
9		    3		     i			   y

11		   4		     g			   y
12		   4		     h			   n

---------------------------------------------------

The result should be like below.

Code: Select all

------------------------------------------------------------
imgID	ProductID	ImageFile	PrimaryFlag
------------------------------------------------------------
4		    2		     d			   n
5		    2		     e			   y
6		    2		     f			   y

7		    3		     g			   y
8		    3		     h			   n
9		    3		     i			   y
------------------------------------------------------------
Thanks in advance,
regards
Waqas
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Have you had a bash yet?

This would work

Code: Select all

SELECT
  t1.*
FROM
  `tbl_images` AS t1
WHERE
  t1.productid IN
  (SELECT t2.productid
   FROM tbl_images AS t2
   WHERE t2.primaryflag = 'y'
   GROUP BY t2.productid
   HAVING COUNT(t2.productid) > 1);
Problem with that one is that I am not sure if the sub-query is valid SQL standard. I dont know off hand if you have include the COUNT(t2.productid) in the SELECT field list. Although it works if not standards compliant it may break in the future

Another option is

Code: Select all

SELECT
  t1.*
FROM
  `tbl_images` AS t1
  INNER JOIN (SELECT t2.productid, COUNT(t2.productid)
        FROM tbl_images AS t2
        WHERE t2.primaryflag = 'y'
        GROUP BY t2.productid
        HAVING COUNT(t2.productid) > 1) AS t3
  ON (t1.productid = t3.productid);
I have not compared the efficiency of these as not at home, so might be worth running an explain against each.

Mike
waqas_punjabian
Forum Commoner
Posts: 67
Joined: Wed Aug 10, 2005 9:53 am

Post by waqas_punjabian »

Thanks Mike

The later Query (i.e, with join)

that work fine and it's faster than the previous one.

The results were as follows:

with join query: 0.0059 sec
with other query: 5.4566 sec

a hell of difference :D

This query helped me a lot and shortened my work up to 1/3.
Well, thanks a lot for your help & kind response.


Regards,

Waqas
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

welcome
Post Reply