Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

combining LEFT OUTER JOIN and WHERE

 
Mike Curwen
Ranch Hand
Posts: 3695
IntelliJ IDE Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic