Joining tables and entries in one to permute with number of categories in other

I have a table of raw data that I can sort by say Division and Category. I have 11 Divisions and 4 Categories but this might not be reflected by the data in the table because there might be no entires for every combination.

However I want to generate output that presents all sets of Divisions grouped by Category, that is 11 entries for Category1, 11 for Category2, and so on.

Right now I'm using a subquery to create a table that contains all the Divisions I'm interested and I right join that with my source table. But it doesn't work, I still get a variable number of divisions for each category depending on whether that division-category link exists in my main table.

What's there to do?

To give a little more detail:

Given Category1, Category2, Category3, and Division1, Division3

I would like to get

Division1 Category1
Division2 Category1
Division1 Category2
Division2 Category2
Division1 Category3
Division2 Category3

What I'm getting now is

Division1 Category1
Division2 Category1
Division2 Category2
Division1 Category3

Depending on what entries actually exist in the source table.
|||With SQL 2005, use a CROSS JOIN.|||I just realized how misleading my post was...I may have oversimplified (hopefully I just think I did) the problem.

The thing is, I need to join the entries in that table with the other one based on a key. For example

Table1:

Key Col2 Col3 ... DivisionID Col4 Col5 etc
1 a a 1 a a
2 b b 2 a a
3 c c 5 a a
4 d d 4 a a
Table 2:

DivisionID DivisionName
1 Name1
2 Name2
3 Name3

Now I need to achieve that functionality in the light of me wanting to extract and aggregate some data from table 1.

Would Cross joining between Category and Division and Right joining the result with my original table do the trick?
|||

A CROSS JOIN will provide you all possible combinations from the two tables joined (Division and Category).

You could then join that resultset with another table (JOIN type as necessary -EQUI, LEFT, RIGHT).

|||

I'm still not clear.

Is there a reason a LEFT join on DivisionID will not work?

Adamus

|||It doesn't work because it doesn't generate the layout I desire (check my post above). In terms of correctness yeah, it works, but in terms of layout it doesn't because it only generates a subset of the entries I need.

Whether there is an entry to link the key of that table to the Division X and Category Y or not I want in my output to have the entry

Division Category SomeDataColumn
X Y 0
|||

So I understand or still misunderstand:

Table1:

Key Col2 Col3 ... DivisionID Col4 Col5 etc
1 a a 1 a a
2 b b 2 a a
3 c c 5 a a
4 d d 4 a a

Table 2:
DivisionID DivisionName
1 Name1
2 Name2
3 Name3

If Key 1 has 5 divisions, and 5 divisions is the most divisions any key can have, you want all keys to reflect having 5 divisions whether there's information associated or not?

Adamus

|||Yes, that is correct.

I think I may have figured out a way to make that happen. I performed a cross join between the tables 1 & 2 to get the sets I was interested to and right joined that to my raw date table based on the Category criteria. I seem to be getting the layout I need but I still have to determine if all the information that's supposed to get in, really does get in.

Thanks for your input everyone.