CODEWITHSUNDEEP
mysqlsql
codingManiac
codingManiac

Reputation: 1700

Comparing counts across two tables

Given two tables...

tblEvents (fldEventID, fldAECap, fldDWCap, fldWSCap)
tblSignUps (fldSignUpID, fldEventID, fldPosition)

where a value of 1 in fldPosition relates to fldAECap, a value of 2 relates to fldDWCap, a value of 3 relates to fldWSCap; I need to prevent any new records from being inserted into tblSignUps if the count of fldPosition values is equal to each related CAP value. For instance, we may only have 3 allotted positions for fldAECAP and that position value occurs 3 times in fldPosition for that given EventID

The basic logic would come down to...

INSERT INTO tblSignUps
VALUES(NULL,12,3)
WHERE fldWSCap > COUNT(fldPosition(3))

How would I do this?

Upvotes: 0

Views: 72

Answers (1)

Aris2World
Aris2World

Reputation: 1234

A way to write the required basic logic is this:

INSERT INTO tblSignUps
select NULL,12,3 from dual
where exists (
   select * from tblEvents where
     fldEventId = 12 and
     fldWSCap > (select count(*) from tblSignUps where fldEventId = 12 and fldPosition = 3)
  );

Obviously this works only for the field fldWSCap. The others two require a similar statement where the fld*Cap change according to the fldPosition value.

Upvotes: 1

Related Questions