SQLTeam.com | Weblogs | Forums

Condition check in query


#1

I have 3 tables

table - items

itemid int , item varchar(150)

table - Itemdetails

itemdetailid int ,categoryid int, itemcode varchar(100), itemid int, time varchar(25)
remarks text, stage varchar(50)
( this stage column includes only OFF STAGE , ON STAGE , GROUP)

table - Registration

regid numeric(18, 0)
schoolid numeric(18, 0)
itemid int
name varchar(1000)
classid int
adminno varchar(1000)
studadminno varchar(1000)
status varchar(15)

Before inserting registrations i have to check 3 conditions in the procedure.
1 . One student shall participate in maximum of 5 single items (other than all group items are considered as single items from the stage column in itemdetails table).

2.One student can participate in 2 Group items only.
3.Of the single items only 3 shall be ON STAGE items (can check ON STAGE from the itemdetails table)

How to check these conditions before insert query ? Can anyone please help me.


#2

Please provide table descriptions as create statements, scenarios where registration should succeed and fail (with sampledata as insert statements).