SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Scotland
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Massive query, Inner join query doubt.

    I have four tables. am taking the counts from the meals table according to the criteria from 3 tables. but i need to get
    the sum from one more table called 'othercashreceived' which the common reference is the school id. pls advice me how to do this?.


    Code:
    SELECT s.name,
     
    COUNT(CASE WHEN typeCode = '1' AND pp.year != '9' THEN 1 ELSE NULL END) AS QTYA,
    CONCAT( '', FORMAT( SUM(CASE WHEN typeCode = '1' AND pp.year != '9' THEN price ELSE 0 END),2)) AS STD,
     
    COUNT(CASE WHEN typeCode = '1' AND pp.year = '9' THEN 1 ELSE NULL END) AS QTYB,
    CONCAT( '', FORMAT( SUM(CASE WHEN typeCode = '1' AND pp.year = '9' THEN price ELSE 0 END),2)) AS NRY,
     
    COUNT(CASE WHEN typeCode in ('3') THEN 1 ELSE NULL END) AS QTYC,
    CONCAT( '', FORMAT( SUM(CASE WHEN typeCode IN ('3') THEN price ELSE 0 END),2)) AS ADT,
     
    COUNT(CASE WHEN typeCode = '2' AND pp.year != '9' THEN 1 ELSE NULL END) AS QTYD,
    CONCAT( '', FORMAT( SUM(CASE WHEN typeCode = '2' AND pp.year != '9' THEN price ELSE 0 END),2)) AS FREESTD,
     
    COUNT(CASE WHEN typeCode = '2' AND pp.year = '9' THEN 1 ELSE NULL END) AS QTYE,
    CONCAT( '', FORMAT( SUM(CASE WHEN typeCode = '2' AND pp.year = '9' THEN price ELSE 0 END),2)) AS FREENRY,
     
    COUNT(CASE WHEN typeCode IN ('4') THEN 1 ELSE NULL END) AS QTYF,
    CONCAT( '', FORMAT( SUM(CASE WHEN typeCode IN ('4') THEN price ELSE 0 END),2)) AS FREEADT,
     
    CONCAT( '', FORMAT( SUM(o.amount),2)) AS OTHERCASH,
     
    COUNT(CASE WHEN typeCode = '1' AND pp.year != '9' THEN 1 ELSE NULL END)
    +
    COUNT(CASE WHEN typeCode = '1' AND pp.year = '9' THEN 1 ELSE NULL END)
    +
    COUNT(CASE WHEN typeCode in ('3') THEN 1 ELSE NULL END)
    AS SUBTOTAL
     
    FROM
    meals AS m
    INNER JOIN parents AS p ON m.parent_id = p.id
    INNER JOIN pupils AS pp ON m.pupil_id = pp.id
    INNER JOIN schools AS s ON m.school_id = s.id
    INNER JOIN othercashrecieved as o ON o.school_id = s.id
    WHERE
    m.date BETWEEN "20110130" AND "20110205"
    AND o.weekStart="20110130"
    GROUP BY s.NAME
    -- m.school_id =4 AND
    Last edited by DaveMaxwell; Mar 7, 2011 at 11:19. Reason: added code tags for readability...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you need to get what sum from that table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Scotland
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Following is the query but with this query i need to add a sum of amount from othercashreceived table. reference is school_id. tq

    Table name: othercashreceived
    Field name : amount

    Code:
    SELECT s.name,
     
    COUNT(CASE WHEN typeCode = '1' AND pp.year != '9' THEN 1 ELSE NULL END) AS QTYA,
    CONCAT( '', FORMAT( SUM(CASE WHEN typeCode = '1' AND pp.year != '9' THEN price ELSE 0 END),2)) AS STD,
     
    COUNT(CASE WHEN typeCode = '1' AND pp.year = '9' THEN 1 ELSE NULL END) AS QTYB,
    CONCAT( '', FORMAT( SUM(CASE WHEN typeCode = '1' AND pp.year = '9' THEN price ELSE 0 END),2)) AS NRY,
     
    COUNT(CASE WHEN typeCode in ('3') THEN 1 ELSE NULL END) AS QTYC,
    CONCAT( '', FORMAT( SUM(CASE WHEN typeCode IN ('3') THEN price ELSE 0 END),2)) AS ADT,
     
    COUNT(CASE WHEN typeCode = '2' AND pp.year != '9' THEN 1 ELSE NULL END) AS QTYD,
    CONCAT( '', FORMAT( SUM(CASE WHEN typeCode = '2' AND pp.year != '9' THEN price ELSE 0 END),2)) AS FREESTD,
     
    COUNT(CASE WHEN typeCode = '2' AND pp.year = '9' THEN 1 ELSE NULL END) AS QTYE,
    CONCAT( '', FORMAT( SUM(CASE WHEN typeCode = '2' AND pp.year = '9' THEN price ELSE 0 END),2)) AS FREENRY,
     
    COUNT(CASE WHEN typeCode IN ('4') THEN 1 ELSE NULL END) AS QTYF,
    CONCAT( '', FORMAT( SUM(CASE WHEN typeCode IN ('4') THEN price ELSE 0 END),2)) AS FREEADT,
     
    -- CONCAT( '', FORMAT( SUM(CASE WHEN o.weekStart='20110130' THEN o.amount ELSE 0 END),2)) AS OTHERCASH,
    CONCAT( '', FORMAT( SUM(o.amount),2)) AS OTHERCASH,
     
    COUNT(CASE WHEN typeCode = '1' AND pp.year != '9' THEN 1 ELSE NULL END)
    +
    COUNT(CASE WHEN typeCode = '1' AND pp.year = '9' THEN 1 ELSE NULL END)
    +
    COUNT(CASE WHEN typeCode in ('3') THEN 1 ELSE NULL END)
    AS SUBTOTAL
     
    FROM
    othercashrecieved as o, meals AS m
    INNER JOIN parents AS p ON m.parent_id = p.id
    INNER JOIN pupils AS pp ON m.pupil_id = pp.id
    INNER JOIN schools AS s ON m.school_id = s.id
    -- INNER JOIN othercashrecieved as o ON o.school_id = m.school_id
    WHERE
    o.school_id = m.school_id
    AND o.weekStart="20110130"
    AND m.date BETWEEN "20110130" AND "20110205"
    GROUP BY s.NAME
    -- m.school_id =4 AND
    Last edited by DaveMaxwell; Mar 7, 2011 at 11:18. Reason: added code tags for thread readability...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT s.name
         , COUNT(CASE WHEN typeCode = '1' 
                       AND pp.year != '9' 
                      THEN 1 ELSE NULL END) AS QTYA
         , CONCAT( '', FORMAT( 
             SUM(CASE WHEN typeCode = '1' 
                       AND pp.year != '9' 
                      THEN price ELSE 0 END),2)) AS STD
         , COUNT(CASE WHEN typeCode = '1' 
                       AND pp.year = '9' 
                      THEN 1 ELSE NULL END) AS QTYB
         , CONCAT( '', FORMAT( 
             SUM(CASE WHEN typeCode = '1' 
                       AND pp.year = '9' 
                      THEN price ELSE 0 END),2)) AS NRY
         , COUNT(CASE WHEN typeCode in ('3') 
                      THEN 1 ELSE NULL END) AS QTYC
         , CONCAT( '', FORMAT( 
             SUM(CASE WHEN typeCode IN ('3') 
                      THEN price ELSE 0 END),2)) AS ADT
         , COUNT(CASE WHEN typeCode = '2' 
                       AND pp.year != '9' 
                      THEN 1 ELSE NULL END) AS QTYD
         , CONCAT( '', FORMAT( 
             SUM(CASE WHEN typeCode = '2' 
                       AND pp.year != '9' 
                      THEN price ELSE 0 END),2)) AS FREESTD
         , COUNT(CASE WHEN typeCode = '2' 
                       AND pp.year = '9' 
                      THEN 1 ELSE NULL END) AS QTYE
         , CONCAT( '', FORMAT( 
             SUM(CASE WHEN typeCode = '2' 
                       AND pp.year = '9' 
                      THEN price ELSE 0 END),2)) AS FREENRY
         , COUNT(CASE WHEN typeCode IN ('4') 
                      THEN 1 ELSE NULL END) AS QTYF
         , CONCAT( '', FORMAT( 
             SUM(CASE WHEN typeCode IN ('4') 
                      THEN price ELSE 0 END),2)) AS FREEADT
         , CONCAT( '', FORMAT(o.sum_o_amount,2)) AS OTHERCASH
         , COUNT(CASE WHEN typeCode = '1' 
                       AND pp.year != '9' 
                      THEN 1 ELSE NULL END) +
           COUNT(CASE WHEN typeCode = '1' 
                       AND pp.year = '9' 
                      THEN 1 ELSE NULL END) +
           COUNT(CASE WHEN typeCode in ('3') 
                      THEN 1 ELSE NULL END)  AS SUBTOTAL
     FROM meals AS m
    INNER
      JOIN parents AS p 
        ON p.id = m.parent_id
    INNER
      JOIN pupils AS pp 
        ON pp.id = m.pupil_id
    INNER
      JOIN schools AS s 
        ON s.id = m.school_id
    INNER  
      JOIN ( SELECT school_id
                  , SUM(amount) AS sum_o_amount
               FROM othercashrecieved 
              WHERE weekStart = '20110130'
             GROUP
                 BY school_id ) as o 
        ON o.school_id = s.id
     WHERE m.date BETWEEN '20110130' AND '20110205'
    GROUP 
        BY s.NAME
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Scotland
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    exactly, if i dont joint othercashreceived, it returns 53 rows. if joint othercashreceived it only returns if othercashreceived .school id in the meals table. i want all the 53 rows and if there is any record in the othercashreceived i expect the othercashreceived.sum to be there. pls help. tq

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    use a LEFT OUTER JOIN instead of INNER JOIN for any relationships which are optional
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard webcosmo's Avatar
    Join Date
    Oct 2007
    Location
    Boston, MA
    Posts
    1,480
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Have you thought of using INDEXED VIEWS?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by webcosmo View Post
    Have you thought of using INDEXED VIEWS?
    not sure whom this is addressed to, but could you please give a bit more information about how that would apply in this scenario
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Scotland
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have two tables called meals and nolivemeals. if ther is no meals transaction from the Meals table, i need to take the relevant record from the notlivemeals table. i got two sets of query one return values from meals and another one from notlive meals. how to combine in to one?

    Query A:
    Code:
    SELECT name
    , cashpupil AS CASHPUPILMEALS
    , cashpupil * price_pupil AS CASHPUPILMEALSAMOUNT
    , cashnursery AS CASHNURSERYMEALS
    , cashnursery * price_nursery AS CASHNURSERYMEALSAMOUNT
    , cashadult AS CASHADULTMEALS
    , cashadult * price_adult AS CASHADULTMEALSAMOUNT
    , freepupil AS PUPILFREEMEALS
    , freepupil * price_pupil AS PUPILFREEMEALSAMOUNT
    , freenursery AS NURSERYFREEMEALS
    , freenursery * price_nursery as NURSERYFREEMEALSAMOUNT
    , freeadult AS ADULTFREEMEALS
    , freeadult * price_adult AS ADULTFREEMEALSAMOUNT
    , CONCAT( '', FORMAT(COALESCE(o.sum_o_amount,0),2)) AS OTHERCASH
    , CONCAT( '', FORMAT(COALESCE(nm.cashreceived,0),2)) AS STDCASHRECEIVED
    , 0
    , 0
    , CONCAT( '', FORMAT(COALESCE(BK.sum_act_spent,0),2)) AS ACTUALLSPENT
    , CONCAT( '', FORMAT(COALESCE(INV.sum_inv_amount,0),2)) AS INVOICESTOT
    , COALESCE(WG.sum_tot_hours,0) AS sum_tot_hours
    , 0
    FROM notlivemeals AS nm
    INNER
    JOIN schools AS s
    ON s.id = nm.school_id
    LEFT
    JOIN ( SELECT school_id
    , SUM(amount) AS sum_o_amount
    FROM othercashrecieved
    WHERE weekStart = '20110130'
    GROUP
    BY school_id ) as o
    ON o.school_id = s.id
    LEFT
    JOIN( SELECT cashspent.school_id,
    SUM(amount) as sum_ocs_amount
    FROM cashspent
    WHERE weekstart = '20110130'
    GROUP
    BY school_id ) as CP
    ON CP.school_id = s.id
    LEFT
    JOIN( SELECT banking.school_id
    , (SUM(cash)
    + SUM(cheques)) AS sum_act_spent
    FROM banking
    WHERE weekstart = '20110130'
    GROUP
    BY school_id ) as BK
    ON BK.school_id = s.id
    LEFT
    JOIN( SELECT invoices.school_id,
    SUM(invAmount) as sum_inv_amount
    FROM invoices
    WHERE weekEnterDate = '20110130'
    GROUP
    BY school_id ) as INV
    ON INV.school_id = s.id
    LEFT
    JOIN( SELECT wages.school_id,
    SUM(sunHrs+monHrs+tueHrs+wedHrs+thuHrs+friHrs+satHrs) as sum_tot_hours
    FROM wages
    WHERE weekStart = '20110130'
    GROUP
    BY school_id ) as WG
    ON WG.school_id = s.id
    WHERE nm.startdate = '20110130' AND nm.enddate = '20110205'
    AND s.notlive = '1'
    AND s.notininvoice = '0'
    GROUP
    BY s.name
    ORDER
    BY s.name;

    Query B:
    Code:
    SELECT s.name
    , COUNT(CASE WHEN typeCode = '1'
    AND pp.year != '9'
    THEN 1 ELSE NULL END) AS CASHPUPILMEALS
    , CONCAT( '', FORMAT(
    SUM(CASE WHEN typeCode = '1'
    AND pp.year != '9'
    THEN price ELSE 0 END),2)) AS CASHPUPILMEALSAMOUNT
    , COUNT(CASE WHEN typeCode = '1'
    AND pp.year = '9'
    THEN 1 ELSE NULL END) AS CASHNURSERYMEALS
    , CONCAT( '', FORMAT(
    SUM(CASE WHEN typeCode = '1'
    AND pp.year = '9'
    THEN price ELSE 0 END),2)) AS CASHNURSERYMEALSAMOUNT
    , COUNT(CASE WHEN typeCode in ('3')
    THEN 1 ELSE NULL END) AS CASHADULTMEALS
    , CONCAT( '', FORMAT(
    SUM(CASE WHEN typeCode IN ('3')
    THEN price ELSE 0 END),2)) AS CASHADULTMEALSAMOUNT
    , COUNT(CASE WHEN typeCode = '2'
    AND pp.year != '9'
    THEN 1 ELSE NULL END) AS PUPILFREEMEALS
    , CONCAT( '', FORMAT(
    SUM(CASE WHEN typeCode = '2'
    AND pp.year != '9'
    THEN price ELSE 0 END),2)) AS PUPILFREEMEALSAMOUNT
    , COUNT(CASE WHEN typeCode = '2'
    AND pp.year = '9'
    THEN 1 ELSE NULL END) AS NURSERYFREEMEALS
    , CONCAT( '', FORMAT(
    SUM(CASE WHEN typeCode = '2'
    AND pp.year = '9'
    THEN price ELSE 0 END),2)) AS NURSERYFREEMEALSAMOUNT
    , COUNT(CASE WHEN typeCode IN ('4')
    THEN 1 ELSE NULL END) AS ADULTFREEMEALS
    , CONCAT( '', FORMAT(
    SUM(CASE WHEN typeCode IN ('4')
    THEN price ELSE 0 END),2)) AS ADULTFREEMEALSAMOUNT
    , CONCAT( '', FORMAT(COALESCE(o.sum_o_amount,0),2)) AS OTHERCASH
    , CONCAT( '', FORMAT(COALESCE(CR.sum_scr_amount,0),2)) AS STDCASHRECEIVED
    , CONCAT( '', FORMAT(COALESCE(CR.sum_acr_amount,0),2)) AS ADTCASHRECEIVED
    , CONCAT( '', FORMAT(COALESCE(CP.sum_ocs_amount,0),2)) AS OTHERCASHCOLLECTED
    , CONCAT( '', FORMAT(COALESCE(BK.sum_act_spent,0),2)) AS ACTUALLSPENT
    , CONCAT( '', FORMAT(COALESCE(INV.sum_inv_amount,0),2)) AS INVOICESTOT
    , COALESCE(WG.sum_tot_hours,0) AS sum_tot_hours
     
    , COUNT(CASE WHEN typeCode = '1'
    AND pp.year != '9'
    THEN 1 ELSE NULL END) +
    COUNT(CASE WHEN typeCode = '1'
    AND pp.year = '9'
    THEN 1 ELSE NULL END) +
    COUNT(CASE WHEN typeCode in ('3')
    THEN 1 ELSE NULL END) AS SUBTOTAL
    FROM meals AS m
    INNER
    JOIN parents AS p
    ON p.id = m.parent_id
    INNER
    JOIN pupils AS pp
    ON pp.id = m.pupil_id
    INNER
    JOIN schools AS s
    ON s.id = m.school_id
    LEFT
    JOIN ( SELECT school_id
    , SUM(amount) AS sum_o_amount
    FROM othercashrecieved
    WHERE weekStart = '20110130'
    GROUP
    BY school_id ) as o
    ON o.school_id = s.id
    LEFT
    JOIN( SELECT cashreceived.school_id
    , SUM(CASE WHEN pupils.year !=8
    THEN amount ELSE 0 END) AS sum_scr_amount
    , SUM(CASE WHEN pupils.year =8
    THEN amount ELSE 0 END) AS sum_acr_amount
    FROM cashreceived,pupils
    WHERE cashReceived.pupil_id=pupils.id
    AND(cashReceived.date BETWEEN '20110130' AND '20110205')
    GROUP
    BY school_id ) as CR
    ON CR.school_id = s.id
    LEFT
    JOIN( SELECT cashspent.school_id,
    SUM(amount) as sum_ocs_amount
    FROM cashspent
    WHERE weekstart = '20110130'
    GROUP
    BY school_id ) as CP
    ON CP.school_id = s.id
    LEFT
    JOIN( SELECT banking.school_id
    , (SUM(cash)
    + SUM(cheques)) AS sum_act_spent
    FROM banking
    WHERE weekstart = '20110130'
    GROUP
    BY school_id ) as BK
    ON BK.school_id = s.id
    LEFT
    JOIN( SELECT invoices.school_id,
    SUM(invAmount) as sum_inv_amount
    FROM invoices
    WHERE weekEnterDate = '20110130'
    GROUP
    BY school_id ) as INV
    ON INV.school_id = s.id
    LEFT
    JOIN( SELECT wages.school_id,
    SUM(sunHrs+monHrs+tueHrs+wedHrs+thuHrs+friHrs+satHrs) as sum_tot_hours
    FROM wages
    WHERE weekStart = '20110130'
    GROUP
    BY school_id ) as WG
    ON WG.school_id = s.id
    WHERE m.date BETWEEN '20110130' AND '20110205'
    AND notlive = 0
    AND notininvoice = 0
    GROUP
    BY s.name
    ORDER
    BY s.name
    Last edited by DaveMaxwell; Mar 7, 2011 at 11:18. Reason: added code tags...


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
  •