Hi ...
I have been trying to find a way to make several differant table
counters on one request, and therefor tried to use sub queries as my
solution, but I can't make it work and hope someone could help me out
:-(
Lets say I have 3 tables A, B and C and both A and B have a references
to C. I like to make a view that for each C counts the number of
refering A's and B's.
Tables :
CREATE TABLE A (
INTEGER id,
INTEGER ref );
CREATE TABLE B (
INTEGER id,
INTEGER ref );
CREATE TABLE C (
INTEGER id );
As I can see it, the SQL would look somthing like this :
SELECT C.id, A.count, B.count FROM C,
(SELECT COUNT( A.id ) FROM A WHERE A.ref = C.id) AS A,
(SELECT COUNT( B.id ) FROM B WHERE B.ref = C.id) AS B;
But the result is the same count for each row :-(
This is off cause a very simple example.
What have I done wrong ?
/BL