Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006

    Exclamation Unanswered: Show all records from one table for EACH record in another table?


    I have a funding table which has records listed per year for a business case. BUsiness case is the parent table & the funding table is the child table with a one-to-many relationship.
    Each business case can therefore have one funding record listed which shows 1 year or it can have many funding records for a number of years. The years will fall into 2007-2012 as this is the length of business case timeline.

    EG, Business case 1 has funding record 5000 for 2007.
    Business case 2 has funding records 3000 for 2009, 5000 for 2010, 7000 for 2011.

    For a main report that i have, i have to list the year data grouped by the year so i have totals for each year BUT i'm struggling to figure out how to do this because i need to list all possible years in a matrix. Further more, this needs to be grouped by the project lead field so it lists all possible years of funding per person (each project lead can be a lead for 1 or more cases).

    Joe Bloggs (project lead) (lead of 3 business cases grouped in the following totals)
    year - total ()
    2007 - 10000
    2008 - NULL
    2009 - 20000
    2010 - 15000
    2011 - NULL
    2012 - 4000

    Jim Bob (project lead) (lead of 1 business case grouped in the following totals)
    year - total ()
    2007 - NULL
    2008 - NULL
    2009 - 200
    2010 - NULL
    2011 - NULL
    2012 - 3500

    I created a static table to hold the years as records in the table. I then tried to link this table with my funding table as a right outer join to show all records from the year table but this shows the related years and then the additional years as single entiries. I need to seperate the records so ALL years are listed for each project lead & the amounts are summed by year.

    I know this is a bit confusing so i hope i have explained this well. Can anyone help with this one?



  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    you say you've created a static table of years, that's step 1

    step 2 is to have a table of all project leads, and we'll create that "on the fly" from the funding table

    step 3 is to CROSS JOIN years and project leads, in order to get all possible combinations, and step 4 is to do a LEFT OUTER JOIN from those combinations to the actual data to get your results

     SELECT staticyears.yr
         , pl.projectlead
         , SUM(fundamt) as total_amt
      FROM staticyears
      JOIN ( SELECT DISTINCT projectlead
               FROM funding ) AS pl
      JOIN funding
        ON funding.fundyear = staticyears.yr
       AND funding.projectlead = pl.projectlead
        BY staticyears.yr
         , pl.projectlead | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2006
    R937 - you're a star, mate. That method seems to work.
    I was a bit reluctant on using the cross join method as i was getting a weird set of results from it but i added a distinct to the staement & it gives the correct number of year records for each project lead.

    Thanks again.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts