Re: Strange Query

[Bruce Feist]

GERST, MICHAEL (SBCSI) wrote:

Don't know if this has already come through to everyone, if so I apologize.

Ok, here's the deal

Table1 is a master list holding ID numbers, and related information sent by
clients
Table2 is a queue that utilizes Table1.ID numbers, and updates other
information on the current state of a job associated with these ID numbers
within Table2.  This table is used only as a queue, so the Table1.ID numbers
come and go as jobs complete.  There can be multiple instances, or 0
instances of Table1.ID on Table2.
Table3 is a log that contains even more information concerning the Table1.ID
numbers, and associated processing information from Table2, and information
of further processing, shipping status, tracking numbers, etc.  There can be
multiple instances, or 0 instances of the Table1.ID number on Table3.
So, what I am trying to do is this:

     Table1 holds this info(simplified):
           MasterID(Unique)  int 
           NumberRequested   int

     Table2 holds this info(simplified):
           MasterID          int 
           OrderID(Unique)   int

     Table3 holds this info(simplified):
           MasterID          int
           OrderID(Unique)   int
           JobState          bool
What I need to extract, within a single statement, is this:

     For each MasterID on Table1
           What is the NumberRequested
           Does the MasterID exist on Table2, if so, 
                 how many times? (QUEUED)
           Does the MasterID exist on Table3, and if so,
                 How many times with JobState TRUE (GOOD)
                 How many times with JobState FALSE (BAD)
           If the NumberRequested is greater than
                 QUEUED+GOOD, then return a 1 as (UHOH)

So, looking for a return like:

     MASTERID    NUMBERREQUESTED   QUEUED      GOOD  BAD   UHOH
     1           10                5           4     1     1
     2           10                10          0     0     0
     3           10                8           2     1     0
     4           10                6           0     4     1
     5           10                0           10    0     0
     6           10                0           5     5     1
This is what I tried:

SELECT      Table1.MasterID AS MasterID, 
           Table1.numberofcopies AS numberrequested,
           SUM(CASE WHEN TABLE3.jobstate=0 
                 AND TABLE3.MasterID=Table1.MasterID 
                 THEN 1 END) AS good,
           SUM(CASE WHEN Table3.jobstate=1 
                 AND Table3.MasterID=Table1.MasterID  
                 THEN 1 END) AS bad,
           SUM(CASE WHEN Table2.MasterID=Table1.MasterID 
                 THEN 1 END) AS queued,
           (CASE WHEN numberrequested > queued+good 
                 THEN 1 ELSE 0 END) AS uhoh
           FROM Table1, Table2, Table3
           GROUP BY Table1.MasterID;

How about:

SELECT      Table1.MasterID AS MasterID,
            Table1.NumberRequested AS numberrequested,
            SUM(Table3.jobstate) AS good,
            SUM(Table3.jobstate = 0) AS bad,
            COUNT(Table2.MasterID) AS queued,
            Table1.NumberRequested >
COUNT(Table2.MasterID)+Sum(Table3.JobState) AS uhoh
                        FROM Table1 LEFT JOIN Table2 ON Table1.MasterID
= Table2.MasterID
                        LEFT JOIN Table3 ON Table1.MasterID =
Table2.MasterID
            GROUP BY Table1.MasterID, NumberRequested
Bruce Feist



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]