Complex mySQL Query (again)

1
votes

My apologies that the question title is not more specific.

I am working on a system that involves stock control and membership records. The current problem relates to the 'membership packs' that are given to new members on joining; there are a variety of membership types and each one gets a pack with several items in, no two packs are exactly the same but there is some overlap with some products being used in more than one pack. I need to calculate the number of each product that is used in a given batch of new members.

I can create a query that gives me the total of different membership types in a batch.

I can create a query that give me the total of each product required to make one of each of those packs.

I need to create a query that is a combination of both.

The query that gives the total of each type in a batch: 

    SELECT COUNT(*) AS theCount, membershipPackType
    FROM inputBatches
    LEFT JOIN inputActions ON inputActionID = inputBatchAction
    LEFT JOIN members ON memberID = inputBatchMemberID
    LEFT JOIN membershipPacks ON membershipPackType = memberMembershipType
    LEFT JOIN memPack ON memPackInputBatch = inputBatchID
    WHERE memPackBookedOut = 'U' 
    AND inputActionAbbr <> 'E'
    GROUP BY membershipPackType
    ORDER BY membershipPackType;

This query produces the output at the top of the linked image:

The query that gives the total of each product to make one of each of the types from the result of the above query: 

    SELECT COUNT(*) AS theCount, stockItem 
    FROM membershipPackItems 
    LEFT JOIN membershipPacks ON membershipPackNumber = membershipPackType 
    LEFT JOIN stock ON stockNumber = membershipPackItemNo 
    WHERE membershipPackNumber = 11 OR membershipPackNumber = 12 OR membershipPackNumber = 13 OR membershipPackNumber = 14 OR membershipPackNumber = 23 OR membershipPackNumber = 24   OR membershipPackNumber = 25
    GROUP BY stockItem 
    ORDER BY stockNumber;

This query produces the output at the bottom of this image:

http://www.kidderminsterharriers.com/images/query4.png

If I could combine the two queries then I wouldn't have the WHERE clause in the second query that is hard-coded like that. Also, what this second query doesn't allow for is that the membershipPackItems table includes a field for quantity; it assumes that the quantity of each item per pack is 1.

The ultimate aim is to have query that outputs in a similar fashion to the second query but that the column that is currently 'theCount' is a total allows for the number of each item in the pack (ie more than 1 if needed) and then multiplied up by the number of packs in the batch; in this case those items that currently have a count of 7 (there's 7 different types in this batch and those items appear in all of them) would be 62 (there's 62 members total in this batch).

This is the structure of the data tables used: 

CREATE TABLE membershipPackItems (
  membershipPackItemID int(10) NOT NULL auto_increment,
  membershipPackNumber int(7) NOT NULL,
  membershipPackItemNo varchar(6) NOT NULL,
  membershipPackItemQty int(7) NOT NULL,
  PRIMARY KEY  (membershipPackItemID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=71 ;

CREATE TABLE membershipPacks ( membershipPackID int(5) NOT NULL auto_increment, membershipPackTitle varchar(50) default NULL, membershipPackType int(5) default NULL, PRIMARY KEY (membershipPackID) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

CREATE TABLE stock ( stockID int(10) NOT NULL auto_increment, stockNumber int(8) NOT NULL, stockItem varchar(50) NOT NULL, stockNominalNo int(14) NOT NULL, stockVATNo int(3) NOT NULL, stockDecLevel varchar(1) NOT NULL, stockPeriodSold int(14) NOT NULL, stockPeriodSoldValue float NOT NULL, stockPPurchased int(14) NOT NULL, stockYTDSold int(14) NOT NULL, stockYTDSoldValue float NOT NULL, stockYTDPurchased int(14) NOT NULL, stockDefectLevel int(14) NOT NULL, stockCurrentLevel int(14) NOT NULL, stockReOrderLevel int(14) NOT NULL, stockHolding int(14) NOT NULL, stockBackOrderQty int(14) NOT NULL, stockRetail float NOT NULL, stockCost float NOT NULL, stockOrdered int(14) NOT NULL, stockSupplierNo int(7) NOT NULL, stockSupplierStockNo varchar(20) NOT NULL, stockDeliveryDate date NOT NULL, stockDeleted varchar(1) NOT NULL, stockAllowedLeaps varchar(1) NOT NULL, stockCount int(14) NOT NULL, stockCountDate date NOT NULL, stockCountComment varchar(30) NOT NULL, stockGroup1 varchar(4) NOT NULL, stockGroup2 varchar(4) NOT NULL, stockNewStockNo varchar(6) NOT NULL, stockStatus int(3) NOT NULL, PRIMARY KEY (stockID) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1779 ;

CREATE TABLE inputBatches ( inputBatchID int(10) NOT NULL auto_increment, inputBatchInputNumber int(8) NOT NULL, inputBatchMemberID int(8) NOT NULL, inputBatchAction int(5) NOT NULL, inputBatchDate date NOT NULL, PRIMARY KEY (inputBatchID) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=88 ;

CREATE TABLE members ( memberID int(6) NOT NULL auto_increment, memberCentre int(5) NOT NULL, memberMembershipNo int(15) NOT NULL, memberSurname varchar(50) NOT NULL, memberForename varchar(50) NOT NULL, memberSecondName varchar(50) NOT NULL, memberParentTitle int(3) NOT NULL, memberParentSurname varchar(50) NOT NULL, memberParentForename varchar(50) NOT NULL, memberStreet1 varchar(100) NOT NULL, memberStreet2 varchar(100) NOT NULL, memberTown varchar(50) NOT NULL, memberCounty varchar(20) NOT NULL, memberPostcode varchar(10) NOT NULL, memberPhoneSTD varchar(6) NOT NULL, memberPhone varchar(20) NOT NULL, memberMobile varchar(20) NOT NULL, memberEmail varchar(255) NOT NULL, memberDOB date NOT NULL, memberJoined date NOT NULL, memberGender enum('m','f') NOT NULL, memberSibling enum('no','yes') NOT NULL default 'no', memberMembershipType int(3) NOT NULL, memberSpecNeedsNo int(5) NOT NULL, memberPromoNo int(5) NOT NULL, memberDataProtect enum('no','yes') NOT NULL default 'no', memberReceived date NOT NULL, memberMemberType int(4) NOT NULL, memberSendPack enum('no','yes') NOT NULL default 'no', memberSendGift enum('no','yes') NOT NULL default 'no', memberExpire date NOT NULL, memberDespatched date NOT NULL, memberInputNo int(10) NOT NULL, memberSSC int(10) NOT NULL, memberPrevStreet1 varchar(100) NOT NULL, memberPrevStreet2 varchar(100) NOT NULL, memberPrevTown varchar(50) NOT NULL, memberPrevCounty varchar(20) NOT NULL, memberPrevPostcode varchar(10) NOT NULL, memberPrevCentre varchar(5) NOT NULL, memberInvoiced float NOT NULL, memberPaid float NOT NULL, memberSpecNeedsString varchar(255) NOT NULL, memberNotes mediumtext, memberMembershipYear int(3) default '1', PRIMARY KEY (memberID), UNIQUE KEY memberMembershipNo (memberMembershipNo), KEY memberFullName (memberSurname,memberForename), KEY memberSurname (memberSurname), KEY memberForename (memberForename) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=631747 ;

CREATE TABLE memPack ( memPackID int(10) NOT NULL auto_increment, memPackBookedOut varchar(1) NOT NULL, memPackDate date NOT NULL, memPackMembershipNo int(14) NOT NULL, memPackLicenseeNo int(7) NOT NULL, memPackMemTypeNo int(7) NOT NULL, memPackInputNumber int(13) NOT NULL, memPackInputBatch int(10) NOT NULL, memPackCentreNo int(14) NOT NULL, PRIMARY KEY (memPackID) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=13675 ;

2
mysql
Could have been easier to trace the relationships of the columns if foreign keys were used. - ultrajohn

2 Answers

0
votes

You can combine both queries by joining them on membershipPackType.

The INNER JOIN with your first query takes care of the hardcoded WHERE clause.

SQL Statement

SELECT  COUNT(*) AS theCount
        , stockItem 
FROM    membershipPackItems 
        LEFT JOIN membershipPacks ON membershipPackNumber = membershipPackType 
        LEFT JOIN stock ON stockNumber = membershipPackItemNo 
        INNER JOIN (
          SELECT  COUNT(*) AS theCount
                  , membershipPackType
          FROM    inputBatches
                  LEFT JOIN inputActions ON inputActionID = inputBatchAction
                  LEFT JOIN members ON memberID = inputBatchMemberID
                  LEFT JOIN membershipPacks ON membershipPackType = memberMembershipType
                  LEFT JOIN memPack ON memPackInputBatch = inputBatchID
          WHERE   memPackBookedOut = 'U' 
                  AND inputActionAbbr <> 'E'
          GROUP BY 
                  membershipPackType
        ) cnt ON cnt.membershipPackType = membershipPackItems.membershipPackType
GROUP BY stockItem 
ORDER BY stockNumber;

Edit

I have trimmed down the scripts and altered your first query as below in an effort to try and understand what is what.

If you provide some scripts to fill the tables with some data (preferably the data that should become the outputs you've posted), I am willing to have another look at it, otherwise, this is as far as I am prepared to go with this, sorry.

Note: that you should definitly strive for a consistent naming scheme. membershipPackItemNo in one table is stockNumber in an other, Packtype links with PackNumber, memPackInputBatch links with inputBatchID. If not for your own sanity, it would make it much easier for us to figure out what is what.

Note: the scripts have been changed for SQL Server. I don't have a MySQL running.

CREATE TABLE membershipPackItems (
  membershipPackNumber INTEGER NOT NULL,
  membershipPackItemNo varchar(6) NOT NULL,
) 

CREATE TABLE membershipPacks (
  membershipPackType INTEGER default NULL,
) 

CREATE TABLE stock (
  stockNumber INTEGER NOT NULL,
  stockItem varchar(50) NOT NULL,
) 

CREATE TABLE inputBatches (
  inputBatchID INTEGER NOT NULL IDENTITY(1, 1),
  inputBatchMemberID INTEGER NOT NULL,
  inputBatchAction INTEGER NOT NULL,
) 

CREATE TABLE members (
  memberID INTEGER NOT NULL IDENTITY(1, 1),
  memberMembershipType INTEGER NOT NULL,
) 

CREATE TABLE memPack (
  memPackBookedOut varchar(1) NOT NULL,
  memPackInputBatch INTEGER NOT NULL,
) 

SELECT  COUNT(*) AS theCount, st.stockItem 
FROM    stock st
        LEFT OUTER JOIN membershipPackItems mpi ON mpi.membershipPackItemNo = st.stockNumber
        LEFT OUTER JOIN membershipPacks mp ON mp.membershipPackType = mpi.membershipPackNumber
WHERE   mpi.membershipPackNumber = 11
GROUP BY stockItem
0
votes

LOL PSEUDOCODE

  1. Get the StockItem, and its quantity associated for each membershipPackType. The ff is just a pseudocode but I am thinking that you can create the actual SQL query to get what I am trying to get at.
MembershipPackType_Stock = SELECT membershipPackType, 
                    StockItem,  MembershipPackItemQty 
                    FROM MembershipPackItems
                      LEFT JOIN MembershipPacks ON ...
                      LEFT JOIN stock ON ...
  1. Get the number of members for each MembershipPackType in a Batch. The ff SQL query is directly taken from your post.
MembershipPackType_Count =  SELECT COUNT(*) AS MemberCount, membershipPackType
    FROM inputBatches
    LEFT JOIN inputActions ON inputActionID = inputBatchAction
    LEFT JOIN members ON memberID = inputBatchMemberID
    LEFT JOIN membershipPacks ON membershipPackType = memberMembershipType
    LEFT JOIN memPack ON memPackInputBatch = inputBatchID
    WHERE memPackBookedOut = 'U' 
    AND inputActionAbbr <> 'E'
    GROUP BY membershipPackType
    ORDER BY membershipPackType;

3 Then you can easily join MembershipPackType_Stock and MembershipPackType_Count to get the total number for each StockItem. Once again, the ff is just a pseudocode but I hope that you'll get the idea.

SELECT StockItem, SUM( MemberCount * MembershipPackItemQty) 
    FROM MembershipPackType_Stock JOIN MembershipPackType_Count
      ON MembershipPackType = ...
      GROUP BY StockItem

I am not sure if this is what you are asking, but if I understand your question correctly, I think this is it.