Home› DADE
ReportBuilder 22.06 now available including Delphi 12.2 support!
New Blog Posts: Merging Reports - Part 1 and Part 2

Problems with Outer Joins

rbuserrbuser
edited June 2007 in DADE
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.

Comments

This discussion has been closed.