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 ]
Joined: Dec 02, 2000
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 ]
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 ]