is this outer join implementation dependant?

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.SPECIALOFFERCODEAND 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

You had said you have only one row in Campaign, and its SORTORDER value is null. So it makes perfect sense to me that query #1 and query#2 are returning the exact same results.

If I am understanding your needs correctly, try this query, which will make use of the ISNULL function to return the value from the CAMPAIGN table if it is not NULL, otherwise the value from the PROMORATEVIEW table is used (and sorry, I switched it to a LEFT OUTER JOIN as those always make is easier for me to understand):

SELECT DISTINCT ISNULL(CD.MEDIACODE,P.MEDIACODE) AS MEDIACODE, ISNULL(CD.SPECIALOFFERCODE, P.SPECIALOFFERCODE) AS SPECIALOFFERCODE
FROMPROMORATEVIEW AS P LEFT OUTER JOINCAMPAIGN AS CD ONP.MEDIACODE = CD.MEDIACODE AND P.SPECIALOFFERCODE = CD.SPECIALOFFERCODE

|||

Terri,

thanks for your answer.

But I think what you are doing in your query still doesn't solve my problem. I was trying "not to have" the records where the fields have NULL values only, I am trying to supress those records that give "not null join", or in other words just want to have "left or right" outer join excluding the inner join values!?

I am not a sql expert, that's why my question is if I am trying to overpower the sql outer join behavior?

If you think in terms of mathematical setsit would be the exclusive union of two sets e.g.union of two sets without the common points (left or right, two different sets. Sorry if I sound confusing, but I need to figure this out, otherwise I end up creating Views NULLs and the inner join, and then from that View take only the nulls.

thanks,

Avarair

|||

avarair:

But I think what you are doing in your query still doesn't solve my problem. I was trying "not to have" the records where the fields have NULL values only, I am trying to supress those records that give "not null join", or in other words just want to have "left or right" outer join excluding the inner join values!?

OK, that makes it clearer, I think. We'll get this figured out. Since CD.SORTORDER is NULL in your Campaign table, use the other 2 fields, and move them into a WHERE clause:

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
WHERE CD.MEDIACODE IS NULLAND CD.SPECIALOFFERCODE IS NULL

If this isn't what you need, can you reply back with a data example, showing the resultset you'd expect from the data you had already supplied in your first post?

|||

Hi Terri,

thanks a lot for taking the time and giving the correct answer. I got it, and at work I had a meeting with my boss and we came to the conclusion. Long live SQL & OUTER JOIN !

Thanks again,

Avarair

|||

Hi Terri,

thanks a lot for taking the time and giving the correct answer. I got it, and at work I had a meeting with my boss and we came to the conclusion. Long live SQL & OUTER JOIN !

Thanks again,

Avarair

sql