Problem w/ Join in SQL View

I am having problem w/ a Left Join when trying to create a SQL view. I am
trying to get all the records from the left table plus the data from the
right table where it has matching rows. Following is my SQL stmt I am using
to create the view. I am getting data, but only data where the two tables
are equal not getting all of the records from the Left table and then it
returning null values where there is no associated data in the right table.
Any help would be appreciated.
SELECT DISTINCT
TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX = dbo.GL00201.ACTINDX
WHERE (dbo.GL00201.BUDGETID = '2007')
ORDER BY dbo.vwBUDGETACCTS.ACTINDXTry:
SELECT DISTINCT
TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =dbo.GL00201.ACTINDX
AND (dbo.GL00201.BUDGETID = '2007')
ORDER BY dbo.vwBUDGETACCTS.ACTINDX
Also, get rid of the TOP 100 PERCENT. It buys you nothing.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Hutch" <Hutch@.discussions.microsoft.com> wrote in message
news:C1547D5D-BE2E-4CE8-8973-18A6453FA298@.microsoft.com...
I am having problem w/ a Left Join when trying to create a SQL view. I am
trying to get all the records from the left table plus the data from the
right table where it has matching rows. Following is my SQL stmt I am using
to create the view. I am getting data, but only data where the two tables
are equal not getting all of the records from the Left table and then it
returning null values where there is no associated data in the right table.
Any help would be appreciated.
SELECT DISTINCT
TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =dbo.GL00201.ACTINDX
WHERE (dbo.GL00201.BUDGETID = '2007')
ORDER BY dbo.vwBUDGETACCTS.ACTINDX|||The immediate cause of your problem is the WHERE clause. The test
against the right table is eliminating the NULL values from the OUTER
join, reducing it to an inner join. The solution to that is to move
the test to the ON clause of the join.
FROM dbo.vwBUDGETACCTS
LEFT OUTER
JOIN dbo.GL00201
ON dbo.vwBUDGETACCTS.ACTINDX = dbo.GL00201.ACTINDX
AND dbo.GL00201.BUDGETID = '2007'
There are other issues, however. One is the use of TOP 100 PERCENT
and ORDER BY. Views are unordered, and SQL Server only allows use of
ORDER BY in a view when the TOP command is used. This has been
discussed endlessly in these forums, but the bottom line is that TOP
100 PERCENT should be removed from the view, along with the ORDER BY.
Ordering of the result set should be achieved by using an ORDER BY in
the query that references the view.
The second point that concerns me is the use of DISTINCT. There are
many times where DISTINCT is a valuable tool, but too often it is used
to fix a problem with the query or the database design - or even added
in a blind attempt to fix a problem and left in when it makes no
difference. There is no way to tell from the given information what
purpose it serves in this query, but it is worth reviewing why it is
there.
Roy Harvey
Beacon Falls, CT
On Sun, 31 Dec 2006 16:59:00 -0800, Hutch
<Hutch@.discussions.microsoft.com> wrote:
>I am having problem w/ a Left Join when trying to create a SQL view. I am
>trying to get all the records from the left table plus the data from the
>right table where it has matching rows. Following is my SQL stmt I am using
>to create the view. I am getting data, but only data where the two tables
>are equal not getting all of the records from the Left table and then it
>returning null values where there is no associated data in the right table.
>Any help would be appreciated.
>SELECT DISTINCT
> TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
>dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
> dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
>dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
> dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
>FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
> dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =>dbo.GL00201.ACTINDX
>WHERE (dbo.GL00201.BUDGETID = '2007')
>ORDER BY dbo.vwBUDGETACCTS.ACTINDX|||Thanks. That did the trick. I guess I will need to do some research on why
that would not work w/ the 'WHERE' stmt.
On the TOP 100 PERCENT comment, don't I have to use that to use the ORDER BY
stmt?
"Tom Moreau" wrote:
> Try:
> SELECT DISTINCT
> TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
> dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
> dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
> dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
> dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
> FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
> dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX => dbo.GL00201.ACTINDX
> AND (dbo.GL00201.BUDGETID = '2007')
> ORDER BY dbo.vwBUDGETACCTS.ACTINDX
>
> Also, get rid of the TOP 100 PERCENT. It buys you nothing.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Hutch" <Hutch@.discussions.microsoft.com> wrote in message
> news:C1547D5D-BE2E-4CE8-8973-18A6453FA298@.microsoft.com...
> I am having problem w/ a Left Join when trying to create a SQL view. I am
> trying to get all the records from the left table plus the data from the
> right table where it has matching rows. Following is my SQL stmt I am using
> to create the view. I am getting data, but only data where the two tables
> are equal not getting all of the records from the Left table and then it
> returning null values where there is no associated data in the right table.
> Any help would be appreciated.
> SELECT DISTINCT
> TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
> dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
> dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
> dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
> dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
> FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
> dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX => dbo.GL00201.ACTINDX
> WHERE (dbo.GL00201.BUDGETID = '2007')
> ORDER BY dbo.vwBUDGETACCTS.ACTINDX
>|||Thanks for the feedback. I am new to SQL stmts so this info is helpful. If
you do not mind answering one more question, what if I want to filter on more
than one field. Above I am putting in a criteria of '2007' on the BUDGETID
field. If I wanted to also filter on dbo.vwBUDGETACCTS.ADMCTR with the term
'Office' would the syntax be:
AND dbo.GL00201.BUDGETID = '2007', dbo.vwBUDGETACCTS.ADMCTR = 'Office'?
I assume not since I cannot get that syntax to work. Your help is
appreciated.
"Roy Harvey" wrote:
> The immediate cause of your problem is the WHERE clause. The test
> against the right table is eliminating the NULL values from the OUTER
> join, reducing it to an inner join. The solution to that is to move
> the test to the ON clause of the join.
> FROM dbo.vwBUDGETACCTS
> LEFT OUTER
> JOIN dbo.GL00201
> ON dbo.vwBUDGETACCTS.ACTINDX = dbo.GL00201.ACTINDX
> AND dbo.GL00201.BUDGETID = '2007'
> There are other issues, however. One is the use of TOP 100 PERCENT
> and ORDER BY. Views are unordered, and SQL Server only allows use of
> ORDER BY in a view when the TOP command is used. This has been
> discussed endlessly in these forums, but the bottom line is that TOP
> 100 PERCENT should be removed from the view, along with the ORDER BY.
> Ordering of the result set should be achieved by using an ORDER BY in
> the query that references the view.
> The second point that concerns me is the use of DISTINCT. There are
> many times where DISTINCT is a valuable tool, but too often it is used
> to fix a problem with the query or the database design - or even added
> in a blind attempt to fix a problem and left in when it makes no
> difference. There is no way to tell from the given information what
> purpose it serves in this query, but it is worth reviewing why it is
> there.
> Roy Harvey
> Beacon Falls, CT
> On Sun, 31 Dec 2006 16:59:00 -0800, Hutch
> <Hutch@.discussions.microsoft.com> wrote:
> >I am having problem w/ a Left Join when trying to create a SQL view. I am
> >trying to get all the records from the left table plus the data from the
> >right table where it has matching rows. Following is my SQL stmt I am using
> >to create the view. I am getting data, but only data where the two tables
> >are equal not getting all of the records from the Left table and then it
> >returning null values where there is no associated data in the right table.
> >Any help would be appreciated.
> >
> >SELECT DISTINCT
> > TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
> >dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
> > dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
> >dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
> > dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
> >FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
> > dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX => >dbo.GL00201.ACTINDX
> >WHERE (dbo.GL00201.BUDGETID = '2007')
> >ORDER BY dbo.vwBUDGETACCTS.ACTINDX
>|||"Hutch" <Hutch@.discussions.microsoft.com> wrote in message
news:0C119075-A92E-4D07-B3FA-A3FD7E3CBB2E@.microsoft.com...
> Thanks. That did the trick. I guess I will need to do some research on
> why
> that would not work w/ the 'WHERE' stmt.
> On the TOP 100 PERCENT comment, don't I have to use that to use the ORDER
> BY
> stmt?
Only if you're making the mistake of trying to do an ORDER BY in a VIEW.
Don't do that. It doesn't work in SQL 2005.
>
> "Tom Moreau" wrote:
>> Try:
>> SELECT DISTINCT
>> TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
>> dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
>> dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
>> dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
>> dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
>> FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
>> dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =>> dbo.GL00201.ACTINDX
>> AND (dbo.GL00201.BUDGETID = '2007')
>> ORDER BY dbo.vwBUDGETACCTS.ACTINDX
>>
>> Also, get rid of the TOP 100 PERCENT. It buys you nothing.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> ..
>> "Hutch" <Hutch@.discussions.microsoft.com> wrote in message
>> news:C1547D5D-BE2E-4CE8-8973-18A6453FA298@.microsoft.com...
>> I am having problem w/ a Left Join when trying to create a SQL view. I
>> am
>> trying to get all the records from the left table plus the data from the
>> right table where it has matching rows. Following is my SQL stmt I am
>> using
>> to create the view. I am getting data, but only data where the two
>> tables
>> are equal not getting all of the records from the Left table and then it
>> returning null values where there is no associated data in the right
>> table.
>> Any help would be appreciated.
>> SELECT DISTINCT
>> TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
>> dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
>> dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
>> dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
>> dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
>> FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
>> dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =>> dbo.GL00201.ACTINDX
>> WHERE (dbo.GL00201.BUDGETID = '2007')
>> ORDER BY dbo.vwBUDGETACCTS.ACTINDX
>>|||On Sun, 31 Dec 2006 19:57:00 -0800, Hutch wrote:
>Thanks for the feedback. I am new to SQL stmts so this info is helpful. If
>you do not mind answering one more question, what if I want to filter on more
>than one field. Above I am putting in a criteria of '2007' on the BUDGETID
>field. If I wanted to also filter on dbo.vwBUDGETACCTS.ADMCTR with the term
>'Office' would the syntax be:
>AND dbo.GL00201.BUDGETID = '2007', dbo.vwBUDGETACCTS.ADMCTR = 'Office'?
>I assume not since I cannot get that syntax to work. Your help is
>appreciated.
Hi Hutch,
AND dbo.GL00201.BUDGETID = '2007'
AND dbo.vwBUDGETACCTS.ADMCTR = 'Office'
--
Hugo Kornelis, SQL Server MVP|||On Sun, 31 Dec 2006 19:57:00 -0800, Hutch
<Hutch@.discussions.microsoft.com> wrote:
>Thanks for the feedback. I am new to SQL stmts so this info is helpful. If
>you do not mind answering one more question, what if I want to filter on more
>than one field. Above I am putting in a criteria of '2007' on the BUDGETID
>field. If I wanted to also filter on dbo.vwBUDGETACCTS.ADMCTR with the term
>'Office' would the syntax be:
>AND dbo.GL00201.BUDGETID = '2007', dbo.vwBUDGETACCTS.ADMCTR = 'Office'?
>I assume not since I cannot get that syntax to work. Your help is
>appreciated.
AND dbo.GL00201.BUDGETID = '2007'
WHERE dbo.vwBUDGETACCTS.ADMCTR = 'Office'
Tests against the left table in a LEFT OUTER JOIN can appear in the
WHERE clause. Alternately, if you included that test in the ON
clause, it would require an AND, not a comma:
ON dbo.vwBUDGETACCTS.ACTINDX = dbo.GL00201.ACTINDX
AND dbo.GL00201.BUDGETID = '2007'
AND dbo.GL00201.BUDGETID = '2007'
AND dbo.vwBUDGETACCTS.ADMCTR = 'Office'
Roy Harvey
Beacon Falls, CT|||On Mon, 01 Jan 2007 13:18:38 +0100, Hugo Kornelis wrote:
>On Sun, 31 Dec 2006 19:57:00 -0800, Hutch wrote:
>>Thanks for the feedback. I am new to SQL stmts so this info is helpful. If
>>you do not mind answering one more question, what if I want to filter on more
>>than one field. Above I am putting in a criteria of '2007' on the BUDGETID
>>field. If I wanted to also filter on dbo.vwBUDGETACCTS.ADMCTR with the term
>>'Office' would the syntax be:
>>AND dbo.GL00201.BUDGETID = '2007', dbo.vwBUDGETACCTS.ADMCTR = 'Office'?
>>I assume not since I cannot get that syntax to work. Your help is
>>appreciated.
>Hi Hutch,
>AND dbo.GL00201.BUDGETID = '2007'
>AND dbo.vwBUDGETACCTS.ADMCTR = 'Office'
Disregard this. Use Roy's suggestion instead:
AND dbo.GL00201.BUDGETID = '2007'
WHERE dbo.vwBUDGETACCTS.ADMCTR = 'Office'
--
Hugo Kornelis, SQL Server MVP