The updated code gives you all permutations of plot, subplot, and treespecies.
(SELECT DISTINCT treespecies FROM t) gives all treespecies
(SELECT DISTINCT plot FROM t) gives every plot
(SELECT DISTINCT subplot FROM t) gives every subplot
From your example you had 3 plots, numbered: 1,3, and 7,
4 subplots: 1, 2, 5, 6
and 5 treespecies: A, B, C, D, G
So, from your example you should have gotten 3*4*5, or 60 records, and a Y or N
for each.
which is what I get when I run the code.
The first code differed in that you got permutations by plot and subplot:
you still had 5 treespecies, but now you had 4 combinations of plot, subplot:
(1, 1),(3, 2), (7,5), (7,6) so you get 20 records (5*4) each with a Y or N
PS:
I went back and re-read your initial posting and maybe I misunderstood something. The following change will generate the species in a plot that are/arenot in a subplot:
SELECT plot, subplot, treespecies, 'N' AS encountered
FROM
(SELECT DISTINCT plot, treespecies FROM t),
(SELECT DISTINCT subplot FROM t)
;
Now I get 40 data points from your data.
Message was edited by: rwright at Feb 12, 2007 5:27 PM