query for inventory tracking with date parameter as constraint

Discussion in 'MySQL' started by tstrick4, Nov 23, 2010.

0
  1. #1
    Hi, I'm having difficulty coming up with an SQL query that a 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
     
    tstrick4, Nov 23, 2010 IP