help setting up matrix cross-refrence query [modified]

[empulse]

I am not totally sure what the proper terminology for this structure is but the situation is: I have three tables Table One: Category ------------------- CatID (int, index) Category (varchar) Example Values(fruit, vegetable, mineral, language. etc) Table Two: Objects ---------------------- ObjectID (int, index) Parent_Category (int, Foreign Key to CatID) ObjectName (varchar) example Values (Apples, Brocolli, Copper, etc) table Three: Content ------------------------ ContentID (primary key) CatID (FK to table Category) ObjectID (Fk to table Objects) ...OtherContent... I need to construct a select statement that produces something similar to the following format

       Fruit   Vegetable  Mineral  Language 

---

Apple X
Bananna X
French X
Brocolli X
Cherry X
Cabbage X
Iron X

Idealy, I would like to have the ContentID of the correspondig record from the third table (Content) displayed in place of each "X". The goal is to generate an HTML page with an HREF at each "X" that links to a page that retrieves the specific record from the Content table. Any suggestions?

modified on Monday, August 18, 2008 2:05 PM

[DerekFL]

Why no have a category id in the object table with foreign key to category table? Seems like you have a cross ref table which is only needed in a one to many or many to many relationship. Can an object have 2 categories?

[empulse]

There is a foreign key in the Objects table to the Category Table. The example here is a simplified version of a customer requirement. The real data is actually some obscure industrial materials, but the structure I need is what is illustrated. The output will actually be a web page with intersecting points (represented by the 'X's) Each intersection of material and category would be unique. A "Content" Record will have only one "Category" and One "Object."

[Tripathi Swati]

first take Category table in @table or in #table bcoz no of colmns depends upon ur no of rows in category table. then join it with content table - > object table & use switch case for ur specified format.

[Wendelius]

Don't know if this is an option for you but one possibility (which could be easy) is to create a stored procedure in order to return correct result set. Stored procedure could return a table type or a cursor (depending on the needs). This way you would have the full power of T-SQL to use and it would be easier to break the logic in to pieces. Another way (haven't tested it though) could be using pivoting and correlated subqueries. Mika

[Mycroft Holmes]

look into Pivot queries (presuming SQL 2005)

Never underestimate the power of human stupidity RAH