Join on a view without the view

[mr_vodka]

Hi I was wondering what the syntax would be to have a left join from one table to a resulting query without the view. Currently I use a view and it works fine but I was wondering how to achieve this without creating the view.

Here is the current view's SQL statement ( without the create view etc)

VIEW NAME: vw_2009

SELECT
fundinfo.fk_budget_ID,
fundinfo.fk_tiertwo_ID,
Sum(fundlink.fundlink_amt)
FROM (fundinfo LEFT JOIN fundlink on((fundinfo.pk_fund_ID = fundlink.fk_fund_ID`)))
WHERE ((fundlink.date_create <= '2009-03-15') and (fundlink.date_create >= '2009-01-01') and (fundinfo.fundinfo_budg_yr = '2009'))
GROUP by fundinfo.fk_tiertwo_ID

Here is the query that is using that view on the right side.

SELECT
budget.budget_name,
vw_2009.Sum(fundlink.fundlink_amt)
FROM
budget
Left Join vw_2009 ON budget.pk_budget_ID = vw_2009.fk_budget_ID

So how do I combine them into one statement? I tried creating a subquery but I couldnt get it working correctly.

TIA.

[darkagn]

Hi mr_vodka and welcome to DaniWeb :)

Love the username btw! :)

If all you want from the budget table is the budget_name field, you could just try this:

SELECT
fundinfo.fk_budget_ID,
fundinfo.fk_tiertwo_ID,
Sum(fundlink.fundlink_amt),
budget.budget_name
FROM fundinfo 
LEFT JOIN fundlink 
ON fundinfo.pk_fund_ID = fundlink.fk_fund_ID
LEFT JOIN budget
ON budget.pk_budget_ID = fundinfo.fk_budget_ID
WHERE fundlink.date_create <= '2009-03-15' and fundlink.date_create >= '2009-01-01' and fundinfo.fundinfo_budg_yr = '2009'
GROUP by fundinfo.fk_tiertwo_ID, budget.budget_name

Hope this helps,
darkagn

[mr_vodka]

Hi mr_vodka and welcome to DaniWeb :)

Thanks for the welcome and assistance darkagn.

I tried your suggestion but its a no go. It still will not return all the records in the budget table.

I even tried to switch the order of the joins by putting the budget to Fund LEFTJOIN first but it didnt help.

Currently, when I am using the simple LEFTJOIN with the view it will result in 15 records. If I use the statement that you provided, it will only show 3.

I figure that it was a matter or order or parathensis but I just seem to get it correct. I dont want to have to create a temp view just to achieve this. I am hoping that there is a way to do it with one SQL statement.

Thanks again.

[mr_vodka]

Bump... Anyone?

[varmadba]

Try this

SELECT
budget.budget_name,
total.fundlink.fundlink_amt
FROM
budget
Left Join 
(
SELECT
fundinfo.fk_budget_ID,
fundinfo.fk_tiertwo_ID,
Sum(fundlink.fundlink_amt)
FROM (fundinfo LEFT JOIN fundlink on((fundinfo.pk_fund_ID = fundlink.fk_fund_ID`)))
WHERE ((fundlink.date_create <= '2009-03-15') and (fundlink.date_create >= '2009-01-01') and (fundinfo.fundinfo_budg_yr = '2009'))
GROUP by fundinfo.fk_tiertwo_ID
)total
 ON budget.pk_budget_ID = total.fk_budget_ID

[mr_vodka]

Great. Much appreciated. I couldnt get passed the setting of the "total" alias. Thanks for helping me out.