Problems with Outer Joins

rbuser

Using the pubs database that comes with SQL Server 2000 I run the following
query 1:

SELECT authors.au_fname, authors.au_lname, publishers.pub_name
FROM authors LEFT OUTER JOIN publishers
ON authors.city = publishers.city
ORDER BY authors.au_lname ASC, authors.au_fname ASC

The result returned contains 23 rows. Two of the rows (1&3) have a NON NULL
pub_name.

If I run the following query 2

SELECT authors.au_fname, authors.au_lname, publishers.pub_name
FROM authors LEFT OUTER JOIN publishers
ON authors.city = publishers.city
AND publishers.state <> 'CA'
ORDER BY authors.au_lname ASC, authors.au_fname ASC

I get 23 rows returned again. In this case the pub_name is NULL in ALL of
the rows returned.

My problem is this: I can use the Graphical Query Builder to create Query 1
above and get the same results. However I cannot use it to reproduce Query
2. It seems I cannot add a condition to the left outer join clause (in this
case " AND publishers.state <> 'CA' ") to only join publishers where the
state is not CA.

Trying to create Query 2 I actually get this:

SELECT authors.au_fname, authors.au_lname, publishers.pub_name
FROM authors LEFT OUTER JOIN publishers
ON authors.city = publishers.city
WHERE publishers.state <> 'CA'
ORDER BY authors.au_lname ASC, authors.au_fname ASC

Which returns no data at all.

Is this a bug? Or am I doing something wrong?

I can make it work by writing the SQL myself. The problem is once I do that
I can't link my query to any others in the data tab, which I need to do.

Nigel.

nicocizik

Hi Nigel,

The ReportBuilder Query Designer does not support conditional joins on a
specific value like your second query. I'm unsure why adding a Where clause
condition returns no records. This is either a limitaion of SQL Server or
for some reason, it cannot resolve the publishers.state field. Another
option would be to manually edit the queries yourself then manually link
them in code as well.

http://www.digital-metaphors.com/tips/LinkDataviewsInCode.zip

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com