MySQL

Join on a view without the view

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Apr 2009

Posts: 4

Reputation: mr_vodka is an unknown quantity at this point

Solved Threads: 0

mr_vodka

Offline

Newbie Poster

Join on a view without the view

Apr 21st, 2009

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

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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
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.

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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
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.

Last edited by mr_vodka; Apr 21st, 2009 at 2:35 pm.

•

Join Date: Aug 2007

Posts: 793

Reputation: darkagn has a spectacular aura about

Solved Threads: 109

darkagn

Offline

Master Poster

Re: Join on a view without the view

Apr 21st, 2009

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:

 Help with Code Tags 
 mysql Syntax (Toggle Plain Text) 
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
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

There are no stupid questions, only those too stupid to ask for help.
echo is a web developer's best friend.

•

Join Date: Apr 2009

Posts: 4

Reputation: mr_vodka is an unknown quantity at this point

Solved Threads: 0

mr_vodka

Offline

Newbie Poster

Re: Join on a view without the view

Apr 22nd, 2009

•

Originally Posted by darkagn

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.

Last edited by mr_vodka; Apr 22nd, 2009 at 11:38 am.

•

Join Date: Apr 2009

Posts: 4

Reputation: mr_vodka is an unknown quantity at this point

Solved Threads: 0

mr_vodka

Offline

Newbie Poster

Re: Join on a view without the view

Apr 24th, 2009

Bump... Anyone?

•

Join Date: Jun 2008

Posts: 79

Reputation: varmadba is an unknown quantity at this point

Solved Threads: 8

varmadba

Offline

Junior Poster in Training

Re: Join on a view without the view

Apr 29th, 2009

Try this

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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
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

Last edited by varmadba; Apr 29th, 2009 at 8:43 am.

:- Varma

We are Happy to inform launch of a new site with loads of database related information Site offers wide range of functionality Forums,Blogs,Articles,Editorials and much more
http://www.sqllibrarian.info/