This week's book giveaway is in the OO, Patterns, UML and Refactoring forum.
We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line!
See this thread for details.
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


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

I think this is causing your problem:
extended_name =
on your outer join with no match, extended_name will be null, but 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 = OR extended_name IS NULL )
brackets are important to maintain the rest of the query where condition
hope this helps,
[ May 13, 2003: Message edited by: Jamie Robertson ]
I’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link:
subject: combining LEFT OUTER JOIN and WHERE
It's not a secret anymore!