Complex mySQL Query (again)

Answer 1

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