Ad:

MySQL Discussion Thread
Unsolved
Views: 1310

Apr 21st, 2009

Join on a view without the view

Expand Post »

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

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.

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.

Similar Threads

bank system problem !!!! (C++ programming) in C++
How to always view answers on Experts Exchange in Geeks' Lounge
Computed column in view VS trigger on update in MS SQL
image maker in Community Introductions

mr_vodka

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Apr 2009

Permalink

Apr 21st, 2009

Re: Join on a view without the view

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:

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

darkagn

Reputation Points: 395

Solved Threads: 192

Veteran Poster

Offline

1,136 posts
since Aug 2007

Permalink

Apr 22nd, 2009

Re: Join on a view without the view

Click to Expand / Collapse Quote 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.

mr_vodka

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Apr 2009

Permalink

Apr 24th, 2009

Re: Join on a view without the view

Bump... Anyone?

mr_vodka

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Apr 2009

Permalink

Apr 29th, 2009

Re: Join on a view without the view

Try this

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.

varmadba

Reputation Points: 22

Solved Threads: 9

Junior Poster in Training

Offline

83 posts
since Jun 2008

Permalink

May 28th, 2009

Re: Join on a view without the view

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

mr_vodka

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Apr 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Conditional condition

Next Thread in MySQL Forum Timeline: MySQLdb interface problem

DaniWeb Message

Cancel Changes

User Name
Password