Problems with Multiple outer joins for crosstab

[lsgtechuser]

first off there may be a better way to do this, i'm not sure. i'm trying to build a crosstab query off of a bunch of different count queries joined together by unique values. There are 10 total queries being joined, but only one of the queries returns all of the values the data needs to be grouple by. is there a way to shorthand outer joins similar to oracle? the sql i have looks something like this:
select B.[FIELDBLABLA], Acount, Bcount, Ccount, Dcount, Ecount, Fcount, Gcount, Hcount, Icount, Jcount
FROM
(select [FIELDBLABLA],count(*) AS Acount from myfile
group by [FIELDBLABLA]) A ,
(SELECT [FIELDBLABLA],COUNT(*) AS Bcount from myfile
WHERE ....
GROUP BY [FIELDBLABLA]) B ,
(SELECT [FIELDBLABLA],COUNT(*) AS Ccount from myfile
WHERE ....
GROUP BY [FIELDBLABLA]) C,
(SELECT [FIELDBLABLA],COUNT(*) AS Dcount from myfile
WHERE ....
GROUP BY [FIELDBLABLA]) D,
(SELECT [FIELDBLABLA],COUNT(*) AS Ecount from myfile
WHERE ....
GROUP BY [FIELDBLABLA] ) E,
(SELECT [FIELDBLABLA],COUNT(*) AS Fcount from myfile
WHERE ....
GROUP BY [FIELDBLABLA] ) F,
(SELECT [FIELDBLABLA],COUNT(*) AS Gcount from myfile
WHERE ...
GROUP BY [FIELDBLABLA] ) G,
(SELECT [FIELDBLABLA],COUNT(*) AS Hcount from myfile
WHERE ...
GROUP BY [FIELDBLABLA] ) H,
(SELECT [FIELDBLABLA],COUNT(*) AS Icount from myfile
WHERE ...
GROUP BY [FIELDBLABLA] ) I,
(SELECT [FIELDBLABLA],COUNT(*) AS Jcount from myfile
WHERE ...
GROUP BY [FIELDBLABLA] ) J
where
a.FIELDBLABLA = b.FIELDBLABLA
and b.FIELDBLABLA = c.FIELDBLABLA
and c.FIELDBLABLA = d.FIELDBLABLA
and d.FIELDBLABLA = e.FIELDBLABLA
and e.FIELDBLABLA = f.FIELDBLABLA
and f.FIELDBLABLA = g.FIELDBLABLA
and g.FIELDBLABLA = h.FIELDBLABLA
and h.FIELDBLABLA = i.FIELDBLABLA
and i.FIELDBLABLA = j.FIELDBLABLA

This is the innerjoin version, I'm not used to doing multiple outer joins using sql server,I guess i need to do a full outer joins for all these tables to get the result set desired. Any help would be greatly appreciated, if someone has a better suggestion for how to crosstab these counts that would be great too.

 

[vbkris]

from the look of it u want to have different counts from the same table based on a condition.

try this:

select [FIELDBLABLA],
count( case when CONDITIONFORA=TRUE THEN 1 ELSE NULL) Acount,
count( case when CONDITIONFORB=TRUE THEN 1 ELSE NULL) ,
...

FROM myfile
group by [FIELDBLABLA]

keep repeating that for as many counts as u want...

Known is handfull, Unknown is worldfull