permaculture playing cards*
The moose likes JDBC and the fly likes combining LEFT OUTER JOIN and WHERE Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "combining LEFT OUTER JOIN and WHERE" Watch "combining LEFT OUTER JOIN and WHERE" New topic
Author

combining LEFT OUTER JOIN and WHERE

Mike Curwen
Ranch Hand

Joined: Feb 20, 2001
Posts: 3695

So the category_extension has custom data fields. And the category_extension_data table contains "instances" of the custom data.

How do I make a mySQL query (no subqueries) that returns ALL custom fields for a given sub_cat, and ANY of the data from the data table.

I have this so far:
For example, if I have 5 custom fields, but a user only fills out 3 of them, then there are 5 rows in the left table and 3 fields in the right. I want my result set to contain 5 rows, with sql 'null' in the extended_name and extended_data fields. But with the above query, I only see 3. My WHERE clause kills off any rows that make it past the outer join I guess.

Am I making this too hard again?
[ May 07, 2003: Message edited by: Mike Curwen ]
Doanh Nguyen
Ranch Hand

Joined: Dec 02, 2000
Posts: 45
Originally posted by Mike Curwen:
...My WHERE clause kills off any rows that make it past the outer join I guess.

The result from a Left Outer Join should include the rows from the left table that were missing from the inner join.
May I suggest that you remove the WHERE clause to see if 5 rows are indeed returned? If yes, then you need to work on your predicate. Without sample data from 2 tables and the intended result, it's difficult to suggest a code.
[ May 12, 2003: Message edited by: Doanh Nguyen ]
[ May 12, 2003: Message edited by: Doanh Nguyen ]
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Mike,
I think this is causing your problem:
extended_name = ce.name
on your outer join with no match, extended_name will be null, but ce.name can never be null(NOT NULL constraint ). You need to account for the null condition with something like this to replace the above:
( extended_name = ce.name OR extended_name IS NULL )
brackets are important to maintain the rest of the query where condition
hope this helps,
Jamie
[ May 13, 2003: Message edited by: Jamie Robertson ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: combining LEFT OUTER JOIN and WHERE