Hi,

I'm working on a query that will do the following. I've got two
tables: TableA has 4 columns that make up the key (A1, A2, A3, A4).
TableB has 5 key columns; 4 of which are made up of the key from
TableA. TableB's key is (A1, A2, A3, A4, SeqNum). Now, what I want
to do is form a query that will give me a count of all the rows in
TableB with matching A1, A2, A3 and A4 values in TableA. I was able
to solve the problem with a stored procedure using a cursor: basically
looping through rows in TableA and getting a count from TableB for
each. But, I'm curious to see if it's possible to do this with a
single query.

TIA

rnurse (AT) cudbytech (DOT) net (Robert Nurse) writes:

select count(*)
from table_a a,
table_b b
where a.a1 = b.a1
and a.a2 = b.a2
and a.a3 = b.a3
and a.a4 = b.a4

(Since a1, a2, a3, a4 is a key for table_a, there will be at most one
table_a row matching each table_b row, so we can use a join rather
than an exists test.)

Did you mean something else?

--
Ed Avis <ed (AT) membled (DOT) com>

Ed Avis <ed (AT) membled (DOT) com> writes:

Perhaps he wants a count for each primary key?

select a.a1, a.a2, a.a3, a.a4, count(*)
from table_a a, table_b b
where a.a1 = b.a1
and a.a2 = b.a2
and a.a3 = b.a3
and a.a4 = b.a4
group by a1, a2, a3, a4

Hi All,

Actually, the one from Ed is what I was after. But, now, both might
be useful. I was confused about what how to set up the FROM clause:
i.e., what table COUNT(*) would reference: TableA or TableB. But I
see now what's happening now.

Thanks for your responses!


Gregory Bond <gnb (AT) itga (DOT) com.au> wrote

In article <a967f5b9.0401271108.7c4fd76 (AT) posting (DOT) google.com>, Robert Nurse wrote:
Neither actually, it counts the records in the joined set.

--
___ _