Query for inventory status with date parameter constraint

Databases
1

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

2 

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
3

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.

4

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