Help with Relational Division

I am trying to create a matrix of counts to help identify our data holdings.
Thanks to Joe Celko I now know I need to perform relational division to find
datasets that match the criteria set up in a secondary table.
I'd be happy to initially just get the counts for each category, as I could
pass the "DSType" parameter in on the fly.
Problem is I can't figure out how from the "Plane/Hanger" example how to
form the relational division query to come up with the counts. Or even a
view listing the datasets that match each "Category"s "Topic" criteria.
There are 4 main tables involved:
-Datasets that we provide information on.
-Types of data that they hold (Regional, Historical, Global, etc.)
-Topics that describe the datasets (Ocean, Carbon, Sea Level, etc.)
-GOOS Matrix that lists Categories that we want to correlate the datasets to
The SQL script to create the tables and insert data into them is at:
http://oceanic.cms.udel.edu/matrix/..._Tables.sql.txt
A PDF of the relationships between the tables is at:
http://oceanic.cms.udel.edu/matrix/..._GOOSMatrix.pdf
And a PDF of the desired matrix view is at:
http://oceanic.cms.udel.edu/matrix/...trix_Output.pdf
TIA,
D
--
"If Pro is the opposite of Con, does that mean that Congress is the opposite
of Progress?"
--GallegherDoes this help? (using Joes's example):
CREATE TABLE PilotSkills (pilot CHAR(15) NOT NULL, plane CHAR(15) NOT
NULL, PRIMARY KEY (pilot, plane));
CREATE TABLE Hangar (plane CHAR(15) NOT NULL PRIMARY KEY);
INSERT INTO PilotSkills (pilot, plane)
SELECT 'Celko', 'Piper Cub' UNION ALL
SELECT 'Higgins', 'B-52 Bomber' UNION ALL
SELECT 'Higgins', 'F-14 Fighter' UNION ALL
SELECT 'Higgins', 'Piper Cub' UNION ALL
SELECT 'Jones', 'B-52 Bomber' UNION ALL
SELECT 'Jones', 'F-14 Fighter' UNION ALL
SELECT 'Smith', 'B-1 Bomber' UNION ALL
SELECT 'Smith', 'B-52 Bomber' UNION ALL
SELECT 'Smith', 'F-14 Fighter' UNION ALL
SELECT 'Wilson', 'B-1 Bomber' UNION ALL
SELECT 'Wilson', 'B-52 Bomber' UNION ALL
SELECT 'Wilson', 'F-14 Fighter' UNION ALL
SELECT 'Wilson', 'F-17 Fighter' ;
INSERT INTO Hangar (plane)
SELECT 'B-1 Bomber' UNION ALL
SELECT 'B-52 Bomber' UNION ALL
SELECT 'F-14 Fighter' ;
SELECT PS1.pilot,
COUNT(PS1.plane) total_skills,
T.total_planes
FROM PilotSkills AS PS1, Hangar AS H1,
(SELECT COUNT(plane) FROM Hangar) AS T(total_planes)
WHERE PS1.plane = H1.plane
GROUP BY PS1.pilot, T.total_planes ;

> The SQL script to create the tables and insert data into them is at:
> http://oceanic.cms.udel.edu/matrix/..._Tables.sql.txt
That's not up to the task in my opinion, which is why I've not used it.
No alternate keys and mostly nullable columns. IDENTITY should not be
the only key of any table and it's very hard to write and verify
queries against such a schema.
David Portas
SQL Server MVP
--