 | |
#11
 
| |||
| |||
|
Re: Complex query - PLEASE HELP! [Solved] -
02-05-2008
, 05:34 PM
|
Thanks Thomas, That was exactly what i was after! S. |
I think there must be a solution that doesn't require a subquery but
my brains a bit frazzled today. I think it could be something along
these lines, but I'd have to extend the dataset to be sure:
SELECT t1.*
FROM orders t1
LEFT JOIN orders t2
ON t1.customer = t2.customer
AND t1.orderdate < t2.orderdate
AND t2.orderdate < '2007-12-20'
WHERE t2.id IS NULL
HAVING orderdate < '2007-12-20';
#12
| |||
| |||
|
|
Thanks Thomas, That was exactly what i was after! S. |
I think there must be a solution that doesn't require a subquery but
my brains a bit frazzled today. I think it could be something along
these lines, but I'd have to extend the dataset to be sure:
SELECT t1.*
FROM orders t1
LEFT JOIN orders t2
ON t1.customer = t2.customer
AND t1.orderdate < t2.orderdate
AND t2.orderdate < '2007-12-20'
WHERE t2.id IS NULL
HAVING orderdate < '2007-12-20';
#13
| |||
| |||
|
|
Thanks Thomas, That was exactly what i was after! S. |
I think there must be a solution that doesn't require a subquery but
my brains a bit frazzled today. I think it could be something along
these lines, but I'd have to extend the dataset to be sure:
SELECT t1.*
FROM orders t1
LEFT JOIN orders t2
ON t1.customer = t2.customer
AND t1.orderdate < t2.orderdate
AND t2.orderdate < '2007-12-20'
WHERE t2.id IS NULL
HAVING orderdate < '2007-12-20';
#14
| |||
| |||
|
|
On Feb 3, 1:39 am, "S." <sianeag... (AT) gmail (DOT) com> wrote: Thanks Thomas, That was exactly what i was after! S. 5 stars! For that! Too generous! ;-) I think there must be a solution that doesn't require a subquery but my brains a bit frazzled today. I think it could be something along these lines, but I'd have to extend the dataset to be sure: SELECT t1.* FROM orders t1 LEFT JOIN orders t2 ON t1.customer = t2.customer AND t1.orderdate < t2.orderdate AND t2.orderdate < '2007-12-20' WHERE t2.id IS NULL HAVING orderdate < '2007-12-20'; |
depends on the DBMS and the indexes available for the involved tables.
Tomas
#15
| |||
| |||
|
|
On Feb 3, 1:39 am, "S." <sianeag... (AT) gmail (DOT) com> wrote: Thanks Thomas, That was exactly what i was after! S. 5 stars! For that! Too generous! ;-) I think there must be a solution that doesn't require a subquery but my brains a bit frazzled today. I think it could be something along these lines, but I'd have to extend the dataset to be sure: SELECT t1.* FROM orders t1 LEFT JOIN orders t2 ON t1.customer = t2.customer AND t1.orderdate < t2.orderdate AND t2.orderdate < '2007-12-20' WHERE t2.id IS NULL HAVING orderdate < '2007-12-20'; |
depends on the DBMS and the indexes available for the involved tables.
Tomas
#16
| |||
| |||
|
|
On Feb 3, 1:39 am, "S." <sianeag... (AT) gmail (DOT) com> wrote: Thanks Thomas, That was exactly what i was after! S. 5 stars! For that! Too generous! ;-) I think there must be a solution that doesn't require a subquery but my brains a bit frazzled today. I think it could be something along these lines, but I'd have to extend the dataset to be sure: SELECT t1.* FROM orders t1 LEFT JOIN orders t2 ON t1.customer = t2.customer AND t1.orderdate < t2.orderdate AND t2.orderdate < '2007-12-20' WHERE t2.id IS NULL HAVING orderdate < '2007-12-20'; |
depends on the DBMS and the indexes available for the involved tables.
Tomas
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.