improve nested query

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

improve nested query

stevepiercy
Given this query.

     select
         t1.id,
         t1.name
     from
         t1
     where
         name like '%moose%'
     limit 50

While looping through the found record set, I perform a nested
query (SQL/Lasso syntax is simplified):

     select
         count(t2.distributor_id) as count
     from
         t2
     where
         t2.stout_id = #row->find('id')
         OR t2.porter_id = #row->find('id')

This will perform N nested queries, where N equals the LIMIT
value of the outer query, finding the count of distributors that
carry a stout or porter that matches the outer query's row id.  
It works, but performing that many nested queries seems
inefficient to me.

Is there a better query where the column 'count' from the inner
query could be rolled up into the outer query?  I've tried
dozens of permutations with subquery and join, but without success.

Thank you!

--steve

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy              Website Builder              Soquel, CA
<[hidden email]>               <http://www.StevePiercy.com/>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: improve nested query

Bil Corry-3
No idea how close to viable this is, it's been a long time since I've
written SQL, but here's something off the top of my head:

SELECT
t1.name AS productname,
count(founditems.distributor_id) AS productcount
FROM
t1
LEFT JOIN (
t2 AS stout ON (stout.stout_id = t1.id)
CROSS JOIN
t2 AS porter ON (porter.porter_id = t1.id)
) AS founditems
WHERE
t1.name like '%moose&'
GROUP BY
t1.name



- Bil



On Sun, Apr 19, 2015 at 10:04 PM, Steve Piercy - Website Builder <
[hidden email]> wrote:

> Given this query.
>
>     select
>         t1.id,
>         t1.name
>     from
>         t1
>     where
>         name like '%moose%'
>     limit 50
>
> While looping through the found record set, I perform a nested query
> (SQL/Lasso syntax is simplified):
>
>     select
>         count(t2.distributor_id) as count
>     from
>         t2
>     where
>         t2.stout_id = #row->find('id')
>         OR t2.porter_id = #row->find('id')
>
> This will perform N nested queries, where N equals the LIMIT value of the
> outer query, finding the count of distributors that carry a stout or porter
> that matches the outer query's row id.  It works, but performing that many
> nested queries seems inefficient to me.
>
> Is there a better query where the column 'count' from the inner query
> could be rolled up into the outer query?  I've tried dozens of permutations
> with subquery and join, but without success.
>
> Thank you!
>
> --steve
>
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> Steve Piercy              Website Builder              Soquel, CA
> <[hidden email]>               <http://www.StevePiercy.com/>
>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>
>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: improve nested query

Brad Lindsay
In reply to this post by stevepiercy
Steve,

Doesn’t this work:

SELECT t1.id, t1.name,
        (SELECT COUNT(t2.distributor_id) FROM t2 WHERE t2.stout_id=t1.id OR t2.porter_id=t1.id) AS count
FROM t1
WHERE t1.name LIKE '%moose'
LIMIT 50



Brad

On April 19, 2015 at 4:04:41 PM, Steve Piercy - Website Builder ([hidden email]) wrote:

> Given this query.
>  
> select
> t1.id,
> t1.name
> from
> t1
> where
> name like '%moose%'
> limit 50
>  
> While looping through the found record set, I perform a nested
> query (SQL/Lasso syntax is simplified):
>  
> select
> count(t2.distributor_id) as count
> from
> t2
> where
> t2.stout_id = #row->find('id')
> OR t2.porter_id = #row->find('id')
>  
> This will perform N nested queries, where N equals the LIMIT
> value of the outer query, finding the count of distributors that
> carry a stout or porter that matches the outer query's row id.
> It works, but performing that many nested queries seems
> inefficient to me.
>  
> Is there a better query where the column 'count' from the inner
> query could be rolled up into the outer query? I've tried
> dozens of permutations with subquery and join, but without success.
>  
> Thank you!
>  
> --steve
>  
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> Steve Piercy Website Builder Soquel, CA
>  
>  
>  
> #############################################################
>  
> This message is sent to you because you are subscribed to
> the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to:  
> Send administrative queries to  
>  


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: improve nested query

stevepiercy
In reply to this post by Bil Corry-3
I fiddled around with this concept, but it is not yielding desired results.

I think I seek an elusive "correlated subquery" in the FROM clause.
http://dev.mysql.com/doc/refman/5.5/en/from-clause-subqueries.html

    Subqueries in the FROM clause can return a scalar, column, row, or
    table. Subqueries in the FROM clause cannot be correlated subqueries,
    **unless used within the ON clause of a JOIN operation**.

Examples are lacking.  :(

--steve


On 4/19/15 at 10:23 PM, [hidden email] (Bil Corry) pronounced:

> No idea how close to viable this is, it's been a long time since I've
> written SQL, but here's something off the top of my head:
>
> SELECT
> t1.name AS productname,
> count(founditems.distributor_id) AS productcount
> FROM
> t1


> LEFT JOIN (
> t2 AS stout ON (stout.stout_id = t1.id)
> CROSS JOIN
> t2 AS porter ON (porter.porter_id = t1.id)
> ) AS founditems
> WHERE
> t1.name like '%moose&'
> GROUP BY
> t1.name
>
>
>
> - Bil
>
>
>
> On Sun, Apr 19, 2015 at 10:04 PM, Steve Piercy - Website Builder <
> [hidden email]> wrote:
>
> > Given this query.
> >
> >     select
> >         t1.id,
> >         t1.name
> >     from
> >         t1
> >     where
> >         name like '%moose%'
> >     limit 50
> >
> > While looping through the found record set, I perform a nested query
> > (SQL/Lasso syntax is simplified):
> >
> >     select
> >         count(t2.distributor_id) as count
> >     from
> >         t2
> >     where
> >         t2.stout_id = #row->find('id')
> >         OR t2.porter_id = #row->find('id')
> >
> > This will perform N nested queries, where N equals the LIMIT value of the
> > outer query, finding the count of distributors that carry a stout or porter
> > that matches the outer query's row id.  It works, but performing that many
> > nested queries seems inefficient to me.
> >
> > Is there a better query where the column 'count' from the inner query
> > could be rolled up into the outer query?  I've tried dozens of permutations
> > with subquery and join, but without success.
> >
> > Thank you!
> >
> > --steve
> >
> > -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> > Steve Piercy              Website Builder              Soquel, CA
> > <[hidden email]>               <http://www.StevePiercy.com/>
> >
> >
> > #############################################################
> >
> > This message is sent to you because you are subscribed to
> >  the mailing list Lasso [hidden email]
> > Official list archives available at http://www.lassotalk.com
> > To unsubscribe, E-mail to: <[hidden email]>
> > Send administrative queries to  <[hidden email]>
> >
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>   the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy              Website Builder              Soquel, CA
<[hidden email]>               <http://www.StevePiercy.com/>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: improve nested query

stevepiercy
In reply to this post by Brad Lindsay
That was one of my earliest attempts.  It yields the count of
548 in each record, instead of the expected 0, 1, 2, or 3 for
each record.

--steve


On 4/19/15 at 5:10 PM, [hidden email] (Brad Lindsay) pronounced:

>Steve,
>
>Doesn’t this work:
>
>SELECT t1.id, t1.name,
>(SELECT COUNT(t2.distributor_id) FROM t2 WHERE
>t2.stout_id=t1.id OR t2.porter_id=t1.id) AS count
>FROM t1
>WHERE t1.name LIKE '%moose'
>LIMIT 50
>
>
>
>Brad
>
>On April 19, 2015 at 4:04:41 PM, Steve Piercy - Website Builder ([hidden email]) wrote:
>>Given this query.
>>
>>select
>>t1.id,
>>t1.name
>>from
>>t1
>>where
>>name like '%moose%'
>>limit 50
>>
>>While looping through the found record set, I perform a nested
>>query (SQL/Lasso syntax is simplified):
>>
>>select
>>count(t2.distributor_id) as count
>>from
>>t2
>>where
>>t2.stout_id = #row->find('id')
>>OR t2.porter_id = #row->find('id')
>>
>>This will perform N nested queries, where N equals the LIMIT
>>value of the outer query, finding the count of distributors that
>>carry a stout or porter that matches the outer query's row id.
>>It works, but performing that many nested queries seems
>>inefficient to me.
>>
>>Is there a better query where the column 'count' from the inner
>>query could be rolled up into the outer query? I've tried
>>dozens of permutations with subquery and join, but without success.
>>
>>Thank you!
>>
>>--steve
>>
>>-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>>Steve Piercy Website Builder Soquel, CA
>>
>>
>>
>>#############################################################
>>
>>This message is sent to you because you are subscribed to
>>the mailing list Lasso [hidden email]
>>Official list archives available at http://www.lassotalk.com
>>To unsubscribe, E-mail to:  Send administrative queries to
>
>
>#############################################################
>
>This message is sent to you because you are subscribed to
>the mailing list Lasso [hidden email]
>Official list archives available at http://www.lassotalk.com
>To unsubscribe, E-mail to: <[hidden email]>
>Send administrative queries to  <[hidden email]>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy              Website Builder              Soquel, CA
<[hidden email]>               <http://www.StevePiercy.com/>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: improve nested query

Wade Maxfield
> On 4/19/15 at 5:10 PM, [hidden email] (Brad Lindsay) pronounced:
>
>> Steve,
>>
>> Doesn’t this work:
>>
>> SELECT t1.id, t1.name,
>> (SELECT COUNT(t2.distributor_id) FROM t2 WHERE t2.stout_id=t1.id OR t2.porter_id=t1.id) AS count
>> FROM t1
>> WHERE t1.name LIKE '%moose'
>> LIMIT 50
>>

> On 20/04/2015, at 13:41, Steve Piercy - Website Builder <[hidden email]> wrote:
>
> That was one of my earliest attempts.  It yields the count of 548 in each record, instead of the expected 0, 1, 2, or 3 for each record.
>
> --steve
>
>


It doesn't return the same count for each record in my test. I think Brad and I may have made assumptions about the table structure that is different from what you actually have.  This is what I've used:


CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `t1` VALUES ('1', 'moose'), ('2', 'moose'), ('3', 'mooses'), ('4', 'mooselike'), ('5', 'notmoose');


CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `distributor_id` int(11) DEFAULT NULL,
  `stout_id` int(11) DEFAULT NULL,
  `porter_id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `t2` VALUES ('1', '1', '1', '1'), ('2', '1', '2', '2'), ('3', '2', '3', '3'), ('4', '3', '4', '4'), ('5', '4', '5', '5'), ('6', '4', '1', '2'), ('7', '4', '1', '3'), ('8', '5', '2', '5'), ('9', '5', '5', '1'), ('10', '6', '0', '3');


SELECT
        t1.id,
        t1.name,
        (
                SELECT
                        COUNT(t2.distributor_id)
                FROM
                        t2
                WHERE
                        t2.stout_id = t1.id
                OR t2.porter_id = t1.id
        ) AS count
FROM
        t1
WHERE
        t1.name LIKE '%moose%'
LIMIT 50;


Results in

id name count
1 moose 4
2 moose 3
3 mooses 3
4 mooselike 1
5 notmoose 3


Is there anything obvious that is different in your structure?

 - Wade





#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

RE: improve nested query

Tom Brown, Liberty Wines
In reply to this post by stevepiercy
Hi Steve

I use MS SQL and as far as I understand your requirements, this works. You just need a small tweak on your ON clause to change it to OR.

I hope the same works in MySQL... (and I have understood correctly :))

SELECT dbo.prod.name
             ,COUNT(dbo.proddist.id) AS DistroCnt
FROM   dbo.prod LEFT OUTER JOIN dbo.proddist ON
              dbo.proddist.prod1 = dbo.prod.id OR
              dbo.proddist.prod2 = dbo.prod.id
GROUP BY dbo.prod.name
HAVING (dbo.prod.name = N'Widget')

Prod is my t1 and proddist is my t2

Prod
id

Name

Type

1

Widget

prod1

2

Gizmo

prod1

3

Thingy

prod1

4

Jobbie

prod2

5

Wangle

prod2

6

Mangle

prod2

7

Widget

prod2



Proddist
id

name

Prod1

Prod2

1

JiT

1

4

2

Ays Late

1

4

3

OnStp

2

7

4

Shzn

3

6


The query above returns:
Name

DistroCnt

Widget

3
















I wouldn't have structured the data like this myself... a bit limited on permutations?  I guess you don't have a lot of say in that though.

Tom



Tom Brown
IT Manager
Liberty Wines Ltd
T: +44 (0) 20 7819 0304
M: +44 (0) 795 150 8004
Office: +44 (0) 20 7720 5350
www.libertywines.co.uk<http://www.libertywines.co.uk>
[Twitter Logo]<https://twitter.com/liberty_wines>@liberty_wines<https://twitter.com/liberty_wines>



[Liberty Wines]


This message from Liberty Wines Ltd is intended solely for the use of the individual or organisation to whom it is addressed. It may contain privileged or confidential information. If you have received this message in error, please notify the originator immediately. If you are not the intended recipient, you should not use, copy, alter, or disclose the contents of this message. All information or opinions expressed in this message and/or any attachments are those of the author and are not necessarily those of Liberty Wines Ltd or its affiliates. Liberty Wines Ltd accepts no responsibility for loss or damage arising from the use of this e-mail or information contained within it, including damage from viruses or other malware.
Liberty Wines Ltd. 6 Timbermill Way, London, SW4 6LY. Tel: 020 7720 5350, Company Reg No: 3293563, VAT No: 669 2231 21.
This email message has been delivered safely and archived online by Mimecast.


[http://www.libertywines.co.uk/email_resources/enviroment.gif] Please be Paper-Light & consider the environment before printing this e-mail or its attachments

From: [hidden email] [mailto:[hidden email]] On Behalf Of Steve Piercy - Website Builder
Sent: 20 April 2015 02:41
To: [hidden email]
Subject: Re: improve nested query

I fiddled around with this concept, but it is not yielding desired results.

I think I seek an elusive "correlated subquery" in the FROM clause.
http://dev.mysql.com/doc/refman/5.5/en/from-clause-subqueries.html<http://dev.mysql.com/doc/refman/5.5/en/from-clause-subqueries.html>

Subqueries in the FROM clause can return a scalar, column, row, or
table. Subqueries in the FROM clause cannot be correlated subqueries,
**unless used within the ON clause of a JOIN operation**.

Examples are lacking. :(

--steve


On 4/19/15 at 10:23 PM, [hidden email]<mailto:[hidden email]> (Bil Corry) pronounced:

> No idea how close to viable this is, it's been a long time since I've
> written SQL, but here's something off the top of my head:
>
> SELECT
> t1.name<http://t1.name> AS productname,
> count(founditems.distributor_id) AS productcount
> FROM
> t1


> LEFT JOIN (
> t2 AS stout ON (stout.stout_id = t1.id)
> CROSS JOIN
> t2 AS porter ON (porter.porter_id = t1.id)
> ) AS founditems
> WHERE
> t1.name<http://t1.name> like '%moose&'
> GROUP BY
> t1.name<http://t1.name>
>
>
>
> - Bil
>
>
>
> On Sun, Apr 19, 2015 at 10:04 PM, Steve Piercy - Website Builder <
> [hidden email]<mailto:[hidden email]>> wrote:
>
> > Given this query.
> >
> > select
> > t1.id,
> > t1.name<http://t1.name>
> > from
> > t1
> > where
> > name like '%moose%'
> > limit 50
> >
> > While looping through the found record set, I perform a nested query
> > (SQL/Lasso syntax is simplified):
> >
> > select
> > count(t2.distributor_id) as count
> > from
> > t2
> > where
> > t2.stout_id = #row->find('id')
> > OR t2.porter_id = #row->find('id')
> >
> > This will perform N nested queries, where N equals the LIMIT value of the
> > outer query, finding the count of distributors that carry a stout or porter
> > that matches the outer query's row id. It works, but performing that many
> > nested queries seems inefficient to me.
> >
> > Is there a better query where the column 'count' from the inner query
> > could be rolled up into the outer query? I've tried dozens of permutations
> > with subquery and join, but without success.
> >
> > Thank you!
> >
> > --steve
> >
> > -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> > Steve Piercy Website Builder Soquel, CA
> > <[hidden email]<mailto:[hidden email]>> <http://www.StevePiercy.com/<http://www.StevePiercy.com/>>
> >
> >
> > #############################################################
> >
> > This message is sent to you because you are subscribed to
> > the mailing list Lasso [hidden email]<mailto:[hidden email]>
> > Official list archives available at http://www.lassotalk.com<http://www.lassotalk.com>
> > To unsubscribe, E-mail to: <[hidden email]<mailto:[hidden email]>>
> > Send administrative queries to <[hidden email]<mailto:[hidden email]>>
> >
>
> #############################################################
>
> This message is sent to you because you are subscribed to
> the mailing list Lasso [hidden email]<mailto:[hidden email]>
> Official list archives available at http://www.lassotalk.com<http://www.lassotalk.com>
> To unsubscribe, E-mail to: <[hidden email]<mailto:[hidden email]>>
> Send administrative queries to <[hidden email]<mailto:[hidden email]>>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy Website Builder Soquel, CA
<[hidden email]<mailto:[hidden email]>> <http://www.StevePiercy.com/<http://www.StevePiercy.com/>>


#############################################################

This message is sent to you because you are subscribed to
the mailing list Lasso [hidden email]<mailto:[hidden email]>
Official list archives available at http://www.lassotalk.com<http://www.lassotalk.com>
To unsubscribe, E-mail to: <[hidden email]<mailto:[hidden email]>>
Send administrative queries to <[hidden email]<mailto:[hidden email]>>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

RE: improve nested query

Tom Brown, Liberty Wines
Hmmm, I should have known my tables would be stripped out. Hopefully you can still make sense of what I have written.

Tom


Tom Brown
IT Manager
Liberty Wines Ltd
T: +44 (0) 20 7819 0304
M: +44 (0) 795 150 8004
Office: +44 (0) 20 7720 5350
www.libertywines.co.uk<http://www.libertywines.co.uk>
[Twitter Logo]<https://twitter.com/liberty_wines>@liberty_wines<https://twitter.com/liberty_wines>



[Liberty Wines]


This message from Liberty Wines Ltd is intended solely for the use of the individual or organisation to whom it is addressed. It may contain privileged or confidential information. If you have received this message in error, please notify the originator immediately. If you are not the intended recipient, you should not use, copy, alter, or disclose the contents of this message. All information or opinions expressed in this message and/or any attachments are those of the author and are not necessarily those of Liberty Wines Ltd or its affiliates. Liberty Wines Ltd accepts no responsibility for loss or damage arising from the use of this e-mail or information contained within it, including damage from viruses or other malware.
Liberty Wines Ltd. 6 Timbermill Way, London, SW4 6LY. Tel: 020 7720 5350, Company Reg No: 3293563, VAT No: 669 2231 21.
This email message has been delivered safely and archived online by Mimecast.


[http://www.libertywines.co.uk/email_resources/enviroment.gif] Please be Paper-Light & consider the environment before printing this e-mail or its attachments

From: [hidden email] [mailto:[hidden email]] On Behalf Of Tom Brown, Liberty Wines
Sent: 20 April 2015 03:31
To: '[hidden email]'
Subject: RE: improve nested query

Hi Steve

I use MS SQL and as far as I understand your requirements, this works. You just need a small tweak on your ON clause to change it to OR.

I hope the same works in MySQL... (and I have understood correctly :))

SELECT dbo.prod.name<http://dbo.prod.name>
,COUNT(dbo.proddist.id) AS DistroCnt
FROM dbo.prod<http://dbo.prod> LEFT OUTER JOIN dbo.proddist ON
dbo.proddist.prod1 = dbo.prod.id<http://dbo.prod.id> OR
dbo.proddist.prod2 = dbo.prod.id<http://dbo.prod.id>
GROUP BY dbo.prod.name<http://dbo.prod.name>
HAVING (dbo.prod.name<http://dbo.prod.name> = N'Widget')

Prod is my t1 and proddist is my t2

Prod
id

Name

Type

1

Widget

prod1

2

Gizmo

prod1

3

Thingy

prod1

4

Jobbie

prod2

5

Wangle

prod2

6

Mangle

prod2

7

Widget

prod2



Proddist
id

name

Prod1

Prod2

1

JiT

1

4

2

Ays Late

1

4

3

OnStp

2

7

4

Shzn

3

6


The query above returns:
Name

DistroCnt

Widget

3
















I wouldn't have structured the data like this myself... a bit limited on permutations? I guess you don't have a lot of say in that though.

Tom



Tom Brown
IT Manager
Liberty Wines Ltd
T: +44 (0) 20 7819 0304
M: +44 (0) 795 150 8004
Office: +44 (0) 20 7720 5350
www.libertywines.co.uk<http://www.libertywines.co.uk><http://www.libertywines.co.uk<http://www.libertywines.co.uk>>
[Twitter Logo]<https://twitter.com/liberty_wines<https://twitter.com/liberty_wines>>@liberty_wines<https://twitter.com/liberty_wines<https://twitter.com/liberty_wines>>



[Liberty Wines]


This message from Liberty Wines Ltd is intended solely for the use of the individual or organisation to whom it is addressed. It may contain privileged or confidential information. If you have received this message in error, please notify the originator immediately. If you are not the intended recipient, you should not use, copy, alter, or disclose the contents of this message. All information or opinions expressed in this message and/or any attachments are those of the author and are not necessarily those of Liberty Wines Ltd or its affiliates. Liberty Wines Ltd accepts no responsibility for loss or damage arising from the use of this e-mail or information contained within it, including damage from viruses or other malware.
Liberty Wines Ltd. 6 Timbermill Way, London, SW4 6LY. Tel: 020 7720 5350, Company Reg No: 3293563, VAT No: 669 2231 21.
This email message has been delivered safely and archived online by Mimecast.


[http://www.libertywines.co.uk/email_resources/enviroment.gif] Please be Paper-Light & consider the environment before printing this e-mail or its attachments

From: [hidden email]<mailto:[hidden email]> [mailto:[hidden email]] On Behalf Of Steve Piercy - Website Builder
Sent: 20 April 2015 02:41
To: [hidden email]<mailto:[hidden email]>
Subject: Re: improve nested query

I fiddled around with this concept, but it is not yielding desired results.

I think I seek an elusive "correlated subquery" in the FROM clause.
http://dev.mysql.com/doc/refman/5.5/en/from-clause-subqueries.html<http://dev.mysql.com/doc/refman/5.5/en/from-clause-subqueries.html><http://dev.mysql.com/doc/refman/5.5/en/from-clause-subqueries.html<http://dev.mysql.com/doc/refman/5.5/en/from-clause-subqueries.html>>

Subqueries in the FROM clause can return a scalar, column, row, or
table. Subqueries in the FROM clause cannot be correlated subqueries,
**unless used within the ON clause of a JOIN operation**.

Examples are lacking. :(

--steve


On 4/19/15 at 10:23 PM, [hidden email]<mailto:[hidden email]<mailto:[hidden email]%3cmailto:[hidden email]>> (Bil Corry) pronounced:

> No idea how close to viable this is, it's been a long time since I've
> written SQL, but here's something off the top of my head:
>
> SELECT
> t1.name<http://t1.name><http://t1.name<http://t1.name>> AS productname,
> count(founditems.distributor_id) AS productcount
> FROM
> t1


> LEFT JOIN (
> t2 AS stout ON (stout.stout_id = t1.id)
> CROSS JOIN
> t2 AS porter ON (porter.porter_id = t1.id)
> ) AS founditems
> WHERE
> t1.name<http://t1.name><http://t1.name<http://t1.name>> like '%moose&'
> GROUP BY
> t1.name<http://t1.name><http://t1.name<http://t1.name>>
>
>
>
> - Bil
>
>
>
> On Sun, Apr 19, 2015 at 10:04 PM, Steve Piercy - Website Builder <
> [hidden email]<mailto:[hidden email]<mailto:[hidden email]%3cmailto:[hidden email]>>> wrote:
>
> > Given this query.
> >
> > select
> > t1.id,
> > t1.name<http://t1.name><http://t1.name<http://t1.name>>
> > from
> > t1
> > where
> > name like '%moose%'
> > limit 50
> >
> > While looping through the found record set, I perform a nested query
> > (SQL/Lasso syntax is simplified):
> >
> > select
> > count(t2.distributor_id) as count
> > from
> > t2
> > where
> > t2.stout_id = #row->find('id')
> > OR t2.porter_id = #row->find('id')
> >
> > This will perform N nested queries, where N equals the LIMIT value of the
> > outer query, finding the count of distributors that carry a stout or porter
> > that matches the outer query's row id. It works, but performing that many
> > nested queries seems inefficient to me.
> >
> > Is there a better query where the column 'count' from the inner query
> > could be rolled up into the outer query? I've tried dozens of permutations
> > with subquery and join, but without success.
> >
> > Thank you!
> >
> > --steve
> >
> > -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> > Steve Piercy Website Builder Soquel, CA
> > <[hidden email]<mailto:[hidden email]<mailto:[hidden email]%3cmailto:[hidden email]>>> <http://www.StevePiercy.com/<http://www.StevePiercy.com/><http://www.StevePiercy.com/<http://www.StevePiercy.com/>>>
> >
> >
> > #############################################################
> >
> > This message is sent to you because you are subscribed to
> > the mailing list Lasso [hidden email]<mailto:[hidden email]<mailto:[hidden email]%3cmailto:[hidden email]>>
> > Official list archives available at http://www.lassotalk.com<http://www.lassotalk.com><http://www.lassotalk.com<http://www.lassotalk.com>>
> > To unsubscribe, E-mail to: <[hidden email]<mailto:[hidden email]<mailto:[hidden email]%3cmailto:[hidden email]>>>
> > Send administrative queries to <[hidden email]<mailto:[hidden email]<mailto:[hidden email]%3cmailto:[hidden email]>>>
> >
>
> #############################################################
>
> This message is sent to you because you are subscribed to
> the mailing list Lasso [hidden email]<mailto:[hidden email]<mailto:[hidden email]%3cmailto:[hidden email]>>
> Official list archives available at http://www.lassotalk.com<http://www.lassotalk.com><http://www.lassotalk.com<http://www.lassotalk.com>>
> To unsubscribe, E-mail to: <[hidden email]<mailto:[hidden email]<mailto:[hidden email]%3cmailto:[hidden email]>>>
> Send administrative queries to <[hidden email]<mailto:[hidden email]<mailto:[hidden email]%3cmailto:[hidden email]>>>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy Website Builder Soquel, CA
<[hidden email]<mailto:[hidden email]<mailto:[hidden email]%3cmailto:[hidden email]>>> <http://www.StevePiercy.com/<http://www.StevePiercy.com/><http://www.StevePiercy.com/<http://www.StevePiercy.com/>>>


#############################################################

This message is sent to you because you are subscribed to
the mailing list Lasso [hidden email]<mailto:[hidden email]<mailto:[hidden email]%3cmailto:[hidden email]>>
Official list archives available at http://www.lassotalk.com<http://www.lassotalk.com><http://www.lassotalk.com<http://www.lassotalk.com>>
To unsubscribe, E-mail to: <[hidden email]<mailto:[hidden email]<mailto:[hidden email]%3cmailto:[hidden email]>>>
Send administrative queries to <[hidden email]<mailto:[hidden email]<mailto:[hidden email]%3cmailto:[hidden email]>>>

#############################################################

This message is sent to you because you are subscribed to
the mailing list Lasso [hidden email]<mailto:[hidden email]>
Official list archives available at http://www.lassotalk.com<http://www.lassotalk.com>
To unsubscribe, E-mail to: <[hidden email]<mailto:[hidden email]>>
Send administrative queries to <[hidden email]<mailto:[hidden email]>>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: improve nested query

Marc Vos-3
In reply to this post by stevepiercy
Hi Steve,

You can put the second select as a column inside the first.

- -
Marc

> Op 19 apr. 2015 om 22:04 heeft Steve Piercy - Website Builder <[hidden email]> het volgende geschreven:
>
> Given this query.
>
>    select
>        t1.id,
>        t1.name
>    from
>        t1
>    where
>        name like '%moose%'
>    limit 50
>
> While looping through the found record set, I perform a nested query (SQL/Lasso syntax is simplified):
>
>    select
>        count(t2.distributor_id) as count
>    from
>        t2
>    where
>        t2.stout_id = #row->find('id')
>        OR t2.porter_id = #row->find('id')
>
> This will perform N nested queries, where N equals the LIMIT value of the outer query, finding the count of distributors that carry a stout or porter that matches the outer query's row id.  It works, but performing that many nested queries seems inefficient to me.
>
> Is there a better query where the column 'count' from the inner query could be rolled up into the outer query?  I've tried dozens of permutations with subquery and join, but without success.
>
> Thank you!
>
> --steve
>
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> Steve Piercy              Website Builder              Soquel, CA
> <[hidden email]>               <http://www.StevePiercy.com/>
>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
> the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: improve nested query

Ke Carlton-3
In reply to this post by Tom Brown, Liberty Wines
No need for the having clause (in MySQL) but pretty much the approach I
would take — depending on the data.

SELECT
        t1.id,
        t1.name,
        COUNT(t2.distributor_id) AS count
FROM t1
LEFT JOIN t2 ON ( t2.stout_id = t1.id OR t2.porter_id = t1.id)
WHERE t1.name LIKE '%moose%'
GROUP BY t1.id
LIMIT 50;


Typically joins win out over sub queries in terms of performance — but
again, it depends.

Ke

On 20 April 2015 at 14:31, Tom Brown, Liberty Wines <[hidden email]>
wrote:

> Hi Steve
>
> I use MS SQL and as far as I understand your requirements, this works. You
> just need a small tweak on your ON clause to change it to OR.
>
> I hope the same works in MySQL... (and I have understood correctly :))
>
> SELECT dbo.prod.name
>              ,COUNT(dbo.proddist.id) AS DistroCnt
> FROM   dbo.prod LEFT OUTER JOIN dbo.proddist ON
>               dbo.proddist.prod1 = dbo.prod.id OR
>               dbo.proddist.prod2 = dbo.prod.id
> GROUP BY dbo.prod.name
> HAVING (dbo.prod.name = N'Widget')
>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: improve nested query

stevepiercy
In reply to this post by Wade Maxfield
Brad and Wade, thank you!  You were both right.  I don't know
what mistake I made originally, then repeated after transcribing
Brad's example, but I got it straightened out on the third try
after Wade's assurance.

Super fast, too: 10ms now vs. 75ms; 1 query vs. up to 51 queries.

--steve


On 4/20/15 at 2:26 PM, [hidden email] (Wade Maxfield) pronounced:

>>On 4/19/15 at 5:10 PM, [hidden email] (Brad Lindsay) pronounced:
>>
>>> Steve,
>>>   Doesn’t this work:
>>>   SELECT t1.id, t1.name,
>>> (SELECT COUNT(t2.distributor_id) FROM t2 WHERE t2.stout_id=t1.id OR
>t2.porter_id=t1.id) AS count
>>> FROM t1
>>> WHERE t1.name LIKE '%moose'
>>> LIMIT 50
>>>
>
>>On 20/04/2015, at 13:41, Steve Piercy - Website Builder <[hidden email]> wrote:
>>
>>That was one of my earliest attempts.  It yields the count of 548 in each record,
>instead of the expected 0, 1, 2, or 3 for each record.
>>
>>--steve
>>
>>
>
>
>It doesn't return the same count for each record in my test. I
>think Brad and I may have made assumptions about the table
>structure that is different from what you actually have.  This
>is what I've used:
>
>
>CREATE TABLE `t1` (
>`id` int(11) NOT NULL,
>`name` varchar(32) DEFAULT NULL,
>PRIMARY KEY (`id`)
>) ENGINE=MyISAM DEFAULT CHARSET=utf8;
>
>INSERT INTO `t1` VALUES ('1', 'moose'), ('2', 'moose'), ('3',
>'mooses'), ('4', 'mooselike'), ('5', 'notmoose');
>
>
>CREATE TABLE `t2` (
>`id` int(11) NOT NULL,
>`distributor_id` int(11) DEFAULT NULL,
>`stout_id` int(11) DEFAULT NULL,
>`porter_id` int(11) DEFAULT NULL
>) ENGINE=MyISAM DEFAULT CHARSET=utf8;
>
>INSERT INTO `t2` VALUES ('1', '1', '1', '1'), ('2', '1', '2',
>'2'), ('3', '2', '3', '3'), ('4', '3', '4', '4'), ('5', '4',
>'5', '5'), ('6', '4', '1', '2'), ('7', '4', '1', '3'), ('8',
>'5', '2', '5'), ('9', '5', '5', '1'), ('10', '6', '0', '3');
>
>
>SELECT
>t1.id,
>t1.name,
>(
>SELECT
>COUNT(t2.distributor_id)
>FROM
>t2
>WHERE
>t2.stout_id = t1.id
>OR t2.porter_id = t1.id
>) AS count
>FROM
>t1
>WHERE
>t1.name LIKE '%moose%'
>LIMIT 50;
>
>
>Results in
>id    name    count
>1 moose   4
>2 moose   3
>3 mooses  3
>4 mooselike   1
>5 notmoose    3
>
>
>Is there anything obvious that is different in your structure?
>
>- Wade
>
>
>
>
>
>#############################################################
>
>This message is sent to you because you are subscribed to
>the mailing list Lasso [hidden email]
>Official list archives available at http://www.lassotalk.com
>To unsubscribe, E-mail to: <[hidden email]>
>Send administrative queries to  <[hidden email]>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy              Website Builder              Soquel, CA
<[hidden email]>               <http://www.StevePiercy.com/>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: improve nested query

stevepiercy
In reply to this post by Ke Carlton-3
Yeah, this was another approach I tried, but the syntax was
escaping me.  Thank you!

Just tried this again transcribing your example, and it works.  
Timing is about the same for this situation, 10ms.

(I'm actually SELECTing a lot more columns and have a longer
WHERE clause to search more columns, otherwise it could be faster.)

--steve


On 4/20/15 at 4:09 PM, [hidden email] (Ke Carlton) pronounced:

>No need for the having clause (in MySQL) but pretty much the approach I
>would take — depending on the data.
>
>SELECT
>t1.id,
>t1.name,
>COUNT(t2.distributor_id) AS count
>FROM t1
>LEFT JOIN t2 ON ( t2.stout_id = t1.id OR t2.porter_id = t1.id)
>WHERE t1.name LIKE '%moose%'
>GROUP BY t1.id
>LIMIT 50;
>
>
>Typically joins win out over sub queries in terms of performance — but
>again, it depends.
>
>Ke
>
>On 20 April 2015 at 14:31, Tom Brown, Liberty Wines <[hidden email]>
>wrote:
>
>>Hi Steve
>>
>>I use MS SQL and as far as I understand your requirements, this works. You
>>just need a small tweak on your ON clause to change it to OR.
>>
>>I hope the same works in MySQL... (and I have understood correctly :))
>>
>>SELECT dbo.prod.name
>>,COUNT(dbo.proddist.id) AS DistroCnt
>>FROM   dbo.prod LEFT OUTER JOIN dbo.proddist ON
>>dbo.proddist.prod1 = dbo.prod.id OR
>>dbo.proddist.prod2 = dbo.prod.id
>>GROUP BY dbo.prod.name
>>HAVING (dbo.prod.name = N'Widget')
>>
>
>#############################################################
>
>This message is sent to you because you are subscribed to
>the mailing list Lasso [hidden email]
>Official list archives available at http://www.lassotalk.com
>To unsubscribe, E-mail to: <[hidden email]>
>Send administrative queries to  <[hidden email]>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy              Website Builder              Soquel, CA
<[hidden email]>               <http://www.StevePiercy.com/>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>