sum a table within a table

[solonzenetzis]

hello

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
)
------------------------------------------------------------------
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
)
------------------------------------------------------------------
but it is not possible
Can anyone help me?

thank you in advance

[Erland Sommarskog]

(so***********@sbzsystems.com) writes:

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
)
------------------------------------------------------------------
but it is not possible
Can anyone help me?

As given the subquery could be written as

select tableB.order, sum(tableB.ok) as s_ok from tableA, tableB
where
tableB.order=tableA.num
and tableB.cust_no=4895
GROUP BY tableB.order
HAVING SUM(tableB.ok) < 5

But something tells me that it should not be necessary to include noth
table twice in the whole query, and that you should use EXISTS instead.
But since I don't have the full picture I cannot suggest an alternative.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Ed Murphy]

so***********@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