aspose file tools
The moose likes JDBC and Relational Databases 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 and Relational Databases
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