# Thread: Show all records from one table for EACH record in another table?

1. Registered User
Join Date
Sep 2006
Posts
30

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

Hi,

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

EG,
year - total (£)
2007 - £10000
2008 - NULL
2009 - £20000
2010 - £15000
2011 - NULL
2012 - £4000

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?

Regards,

Shuja

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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

Code:
``` SELECT staticyears.yr
, SUM(fundamt) as total_amt
FROM staticyears
CROSS
FROM funding ) AS pl
LEFT OUTER
JOIN funding
ON funding.fundyear = staticyears.yr
GROUP
BY staticyears.yr

3. Registered User
Join Date
Sep 2006
Posts
30
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.

Shuja

#### Posting Permissions

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