Query for inventory status with date parameter constraint

[tstrick4]

Hi, I'm having difficulty coming up with an SQL query that I desperately need for an inventory tracking app i'm making for a sound company that sends their speakers on gigs. My table CREATE statements are below:

CREATE TABLE IF NOT EXISTS `SpeakerWorld`.`Speakers` (
`speakerName` CHAR(20) NOT NULL ,
`numOwned` INT NULL ,
`numRepair` INT NULL ,
`numEurope` INT NULL ,
`numAvailable` INT NULL ,
PRIMARY KEY (`speakerName`) )

CREATE TABLE IF NOT EXISTS `SpeakerWorld`.`Gig` (
`gigID` INT NOT NULL AUTO_INCREMENT ,
`gigName` CHAR(45) NULL ,
`loadDate` DATE NULL ,
`returnDate` DATE NULL ,
`returned` TINYINT(1) NULL ,
PRIMARY KEY (`gigID`) )

CREATE TABLE IF NOT EXISTS `SpeakerWorld`.`Hire` (
`gigID` INT NOT NULL ,
`speakerName` CHAR(20) NOT NULL ,
`numSpeakers` INT NULL ,
PRIMARY KEY (`gigID`, `speakerName`) ,
INDEX `gigID` (`gigID` ASC) ,
INDEX `speakerName` (`speakerName` ASC) ,
CONSTRAINT `gigID`
FOREIGN KEY (`gigID` )
REFERENCES `SpeakerWorld`.`Gig` (`gigID` )
CONSTRAINT `speakerName`
FOREIGN KEY (`speakerName` )
REFERENCES `SpeakerWorld`.`Speakers` (`speakerName` )

there is a many to many relationship between Gig and Speakers, so Hire is used as a bridge table. as different speakers are added to a gig, a new row in the hire table is created for each speakerName in a gig.

My problem is this - given a speakerName and a specific date value as parameters, I would like to return the total number of a specific speakerName that are out on a gig on that specific date (that is passed as a parameter). I need this to take into account all gigs' loadDate and returnDate. for example, if i pass the query 2010-11-22, i want to return the sum of "speakerName" that are on gigs that have loadDates prior to 2010-11-22, and returnDates after that.

i know i need to make some sort of join between the Gig and Hire tables, but I'm not sure how to go about this. I would be happy to email an ERD diagram to anyone that thinks they can help.

I would really appreciate any help anyone can offer on this subject, as this functionality is extremely important to the usability of my application

[guido2004]

SELECT sum(Hire.numSpeakers) AS numberOfSpeakers
FROM Gig
INNER JOIN Hire
ON Gig.gigID = Hire.gigID
WHERE Hire.speakerName = 'speakerName'
AND '2010-11-22' BETWEEN Gig.loadDate AND Gig.returnDate

[tstrick4]

Guido, thank you so much, that worked perfectly. I honestly never fully understood inner and outer table joins, and that is where i was becoming confused. You have no idea how much this helps me, I really appreciate it.

[tstrick4]

i have one more question - i'm having difficulty doing this in code, and I was thinking it might just be possible to do within a query.

I want to take the sum that the query you gave me returns, and add it to my speakers table as a derived column. i'd also really like this column to show the numberOwned for each speaker minus the sum that was returned. so this derived column will actually display (numberOwned - numberOfSpeakers).