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."

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

OK This did it for part one. UPDATE T_Log set Commment='Not in main table' WHERE invno NOT IN (SELECT Inventoryno from Inv) AND transactiontype = 'T' Thanks

It's not the select that I am having trouble with it is how to update the transaction log (comments and error)

I have been trying to make this work, but so far no luck. Probably an easy one for someone with a bit more experience. Here is a simplified explanation. I have two tables, the primary table is an inventory table listing items identifed by a unique inventory number and the second is a record of transactions. A record is added to the second table when a transaction occurs that affects an item in the first table. For instance when an item is sold, a transaction is created in the transaction table - "Sold" designated by the letter "S", a new item received is designated with the letter "R" etc. Occasionally an item is listed as "received" that is already in the inventory table or an item is listed as "sold" that is was not entered in the inventory table. I need to do two things: (1) Find the records in the second table (t_log) that do not have a matching inventory number in the Inventory table and insert appropriate text into a comment field. (2) find the records in the transaction table with 'R' transaction type and insert an appropriate comment in the transaction table. In each case I need to set a boolean to to True (error). The tables look like this: ---------------- Inventory Table (Inv) ----------------------- InventoryNo Name, etc. ------------------- TransactionLog (T_Log) ----------------------- InvNo TransactionType Comment Error (Boolean) ------------------ For the records that are in the T_Log but not in the inventory (Table A), the query below displays the records that I want to update, but I don't know how to update just those records. (select Inv.InventoryNo,Inv.ItemName,T_Log.id, T_Log.Invno,t_log.transactiontype, t_log.commment FROM Inv RIGHT OUTER JOIN T_Log on InventoryNo = Invno WHERE Inv.InventoryNo is NULL ) Thanks for any help