• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

combining LEFT OUTER JOIN and WHERE

 
Ranch Hand
Posts: 3695
IntelliJ IDE Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
reply
    Bookmark Topic Watch Topic
  • New Topic