SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Query for inventory status with date parameter constraint

    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Code:
    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. #3
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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).


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •