is this outer join implementation dependant? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

is this outer join implementation dependant?

Author

Topic

avarair
Starting Member

3 Posts

Posted - 2007-01-09 : 21:06:32

Hi all,
Sorry if it looks a little cluttered!
I have these two tables:
CAMPAIGN(
MEDIACODE varchar(10) Unchecked
SPECIALOFFERCODE varchar(15) Unchecked
LAYOUT varchar(10) Unchecked
HEADERTEXT varchar(100) Checked
SORTORDER varchar(10) Unchecked
SORTORDERCOLUMN varchar(50) Unchecked
WIDTH varchar(50) Checked
)
and
PROMORATEVIEW
(
MEDIACODE varchar(10) Checked
SPECIALOFFERCODE varchar(15) Checked
CAMPAIGNCODE varchar(15) Checked
NUMBEROFISSUES smallint Checked
RATE decimal(9, 0) Checked
DESPATCHMETHODCODE varchar(50) Checked
)
CAMPAIGN HAS ONLY ONE ROW:
(CE DG8398 GRID ASC NUMBEROFISSUES NULL)

AND
PROMORATEVIEW
TOO MANY AND HERE A VERY SMALL RANGE OF RECORDS
MEDIACODE SPECIALOFFERCODE CAMPAIGNCODE NUMBEROFISSUES RATE DESPATCHMETHODCODE

(...
CE CER1R02 CER1 12 429 W
CE CER1R03 CER1 24 829 W
CE CER1R03 CER1 12 429 W
CE DG8398 DG8398 12 411 F
CE DG8398 DG8398 12 405 1
CE DG8399 DG8399 12 399 W
CE DG8399 DG8399 12 735 1
CE DG8399 DG8399 12 756 A
CE DG8400 DG8400 12 756 A
CE DG8400 DG8400 12 396 W
...
)
Now the question:
Why these two OUTER JOINS RETURN the same number of rows in My Sql2000 & 2005 express???
1.
SELECT DISTINCT P.MEDIACODE, P.SPECIALOFFERCODE,CD.MEDIACODE
FROM CAMPAIGN AS CD RIGHT OUTER JOIN
PROMORATEVIEW AS P ON P.MEDIACODE = CD.MEDIACODE AND P.SPECIALOFFERCODE = CD.SPECIALOFFERCODE
2.
SELECT DISTINCT P.MEDIACODE, P.SPECIALOFFERCODE,CD.MEDIACODE
FROM CAMPAIGN AS CD RIGHT OUTER JOIN
PROMORATEVIEW AS P ON P.MEDIACODE = CD.MEDIACODE AND P.SPECIALOFFERCODE = CD.SPECIALOFFERCODE AND CD.SORTORDER IS NULL

If you still with me here is what I am trying to do:
to right outer join the campaign with promorateview and the if it's not overriden in campaign( there is no record for MEDIACODE, SPECIALOFFERCODE in campaign ) I will use that view to override the default values.
I suspect that the last statement makes no "influence" on right outer join.
Another work around for me now is that if I create a view that has these values and then filter it with VIEW.SORTORDER IS NULL it does the job but I am trying to get rid of this extra view.

Thanks for your time,
avarair

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 01:00:43

Because you are using OUTER JOIN.

Peter Larsson
Helsingborg, Sweden

avarair
Starting Member

3 Posts

Posted - 2007-01-10 : 01:19:13

Hi Peso,
thanks for the answer, could you, pleaze a little clearer define how outer join works and if my assumption in the post was correct(that it pulls all the records any way!?)

Thanks,
Artur

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 01:31:03

Which OUTER JOIN? Left, right or full?
They are all fully explained in Books Online.

Also, since you are binding the JOIN with MediaCode, if record is matching, the value is the same!

Post your expected output based on the sample data provided above.

Peter Larsson
Helsingborg, Sweden

avarair
Starting Member

3 Posts

Posted - 2007-01-11 : 05:47:31

Hi Peso,
thanks for your time!
After spending some time on this and consulting with my coworker I figured out that my problem was in the precedence of how the statement is carried out.
Right/Left Outer Join gives the NULL records attached and in order to get only the records that are the "NULLs" we simply need to restrict it by WHERE statement, not the condition IS NULL with the AND operator in the JOIN!

Thanks again,
I think I explained clearer what result I was looking for in the query after we figured where the mistake was!
Avarair

quote:
Originally posted by Peso

Which OUTER JOIN? Left, right or full?
They are all fully explained in Books Online.

Also, since you are binding the JOIN with MediaCode, if record is matching, the value is the same!

Post your expected output based on the sample data provided above.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources