SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Threaded View

  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...


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
  •