Complex Query-Help Greatly Appreciated

[Afupi]

Hello DaniWeb,
I have been working on a Maintenance Application. My company builds a specific type of system using specfic types of components. What I need to be able to do is keep track of which component is in which system given a certain event.

ComponentType Table: Ident, Type ( example1: 1, hard drive example2: Motherboard)

EventType Table: Ident, Type(example1: 1,installed example2:2, uninstalled)

System Table: Ident, SerialNum(example1: 1, 1234A example2:2, A4321)

Component Table: Ident, ComponentTypeIdent, SystemIdent, Slot(A system can have 2 HDD, but one motherboard for instance)(example1: 1, 1,1,2 example2:1,2,NULL(not in a system), NULL)

Event Table: Ident,EventTypeIdent,ComponentIdent,SystemIdent (example1: 1,1,1,1 example2 2,2,NULL(This should be set on postback of the component selection dropdown)

I need a query or stored procedure that will check a given EventTypeIdent, know if SystemIdent should be set to null(uninstall) or produce a list of Systems (install)that do not already have all slots filled for a given componentType and SystemIdent=NULL in Component Table.

Here is my latest attempt

 IF @EventTypeIdent=1
 SELECT Components.Ident
 FROM 
    Components 
 WHERE 
 Components.SystemIdent IS NULL 
 AND Components.ComponentTypeIdent=@ComponentTypeIdent

[BitBlt]

Your request is a bit confusing. You appear to be asking for several different things, each of which should require it's own statement or stored proc. Your scenario needs further explanation.

For example, is your Event table used like a transaction to pass forward and trigger some action, or is it used to keep track of events that occur, like a log? It could have a big impact on design.

Also, do you have control over the database design? If so, you might consider creating one (or more?) child tables between System and Component to help resolve the installed/uninstalled issue, rather than using a nullable foreign key. So, you would have a System table, a SystemSlot table (child of System), a Component table and a SystemSlotComponent table (child of SystemSlot and Component). However, if you go that way you have to have Events that reference the specific Component, System and Slot, so you'd have to alter your Event table as well to include a Slot id.

Of course, that last bit is assuming a lot about what your app is supposed to do. Bottom line: please refine your explanation of what your app does, and the specific individual requirements. We will help if we can.

[Afupi]

Apologies, as you can tell from my post, I am confused. Event is a log and a transaction. When an event is inserted I not only need to insert things like eventdate, componentid, and systemid into the event table, but also to change values in other tables.

Example:
Insert all event columns
If event type is install update component.systemid = event.systemid
If eventtype is uninstall component.systemid is NULL

The above psuedo code isn't the issue for me it is handling the knowing how many of each component type is in each system, and what the max number of each componenttype per system.

I guess it isn't the insert truly that is the issue but setting up the parameters and tables for the insertion and the affects throughout the db after the insertion. Come to think of it, I will need to do this for editting events as well. Encapsulation here I come.

[Afupi]

So I solved one problem by placing a PositionType Table and PositionTypeId column in my Components table. The only problem now is for my new Event to show up in the item template of my formview. I suppose this should be asked in the ASP.NET Forum. In case someone is following this thread maybe they can answer it though. I am unsure where to place the SELECT @NewID = @@Identity.

INSERT INTO [Events] ([EventTypeId], [ComponentId], [SystemId], [EventDate], [Notes]) VALUES (@EventTypeId, @ComponentId, @SystemId, @EventDate, @Notes);  SELECT @NewID = @@Identity

UPDATE [Components] SET [PositionTypeId]=@PositionTypeId,[SystemId]=@SystemId
WHERE Id=@ComponentId