Posted by Ed Murphy on 02/22/07 16:57

solonzenetzis@sbzsystems.com wrote:

> I need some help. The following query works perfectly:
> ------------------------------------------------------------------
> select *
> from tableA, tableB
>
> where tableA.num=tableB.order
>
> and tableA.num in (
> select tableB.order from tableA, tableB
> where
> tableB.order=tableA.num
> and tableB.cust_no=4895
> )

FYI, better version of this query:

select *
from tableA
join tableB on tableA.num = tableB.order
where tableB.cust_no = 4895

> I need to do the following:
> ------------------------------------------------------------------
> select *
> from tableA, tableB
>
> where tableA.num=tableB.order
>
> and tableA.num in (
> select tableB.order, sum(tableB.ok) as s_ok from tableA, tableB
> where
> tableB.order=tableA.num
> and tableB.cust_no=4895
> and s_ok<5
> )

select tableA.*, tableB.*
from tableA
join tableB on tableA.num = tableB.order
join (
select order, sum(ok)
from tableB
group by order
having sum(ok) < 5
) B_subset on tableB.order = B_subset.order
where tableB.cust_no = 4895

If you need tableA in the sub-query (i.e. some rows in tableB may match
more or less than one row in tableA), then change the sub-query to

select tableB.order, sum(tableB.ok)
from tableB
join tableA on tableB.order = tableA.num
group by tableB.order
having sum(tableB.ok) < 5

[Back to original message]