Hi,
See these tow tables:
CREATE TABLE ADDRESS
(
CATEGORY INTEGER NOT NULL,
NAME VARCHAR(100) NOT NULL,
KEYINDEX INTEGER NOT NULL,
ADDRESS BLOB SUB_TYPE TEXT SEGMENT SIZE 100,
DATE_MODIFIED VARCHAR(25),
HOST_MODIFIED VARCHAR(25),
USER_MODIFIED VARCHAR(100),
PRIMARY KEY (KEYINDEX)
);
CREATE TABLE CATEGORIES
(
C_NUMBER INTEGER NOT NULL,
NAME VARCHAR(50),
DESCRIPTION VARCHAR(50),
PRIMARY KEY (C_NUMBER)
);
Does anyone know what the right query would be to get me this result:
The Number of the category (C_NUMBER)
The Number of Records in ADDRESS for this category. COUNT(KEYINDEX)
The result *must* contain *all* the categories, even when ADDRESS containts no records that are in that category!
I can get the result without any problem with the categories actually used in ADDRESS, but the should show up all, even when unused.
Using two queries I get the resul but I think that it must be possible to do with one.
SQL Wizards, we need you!
thank you,
yves
See these tow tables:
CREATE TABLE ADDRESS
(
CATEGORY INTEGER NOT NULL,
NAME VARCHAR(100) NOT NULL,
KEYINDEX INTEGER NOT NULL,
ADDRESS BLOB SUB_TYPE TEXT SEGMENT SIZE 100,
DATE_MODIFIED VARCHAR(25),
HOST_MODIFIED VARCHAR(25),
USER_MODIFIED VARCHAR(100),
PRIMARY KEY (KEYINDEX)
);
CREATE TABLE CATEGORIES
(
C_NUMBER INTEGER NOT NULL,
NAME VARCHAR(50),
DESCRIPTION VARCHAR(50),
PRIMARY KEY (C_NUMBER)
);
Does anyone know what the right query would be to get me this result:
The Number of the category (C_NUMBER)
The Number of Records in ADDRESS for this category. COUNT(KEYINDEX)
The result *must* contain *all* the categories, even when ADDRESS containts no records that are in that category!
I can get the result without any problem with the categories actually used in ADDRESS, but the should show up all, even when unused.
Using two queries I get the resul but I think that it must be possible to do with one.
SQL Wizards, we need you!
thank you,
yves