Results 1 to 4 of 4
  1. #1
    Join Date
    May 2010
    Posts
    3

    Unanswered: Joining a table where null values exist

    Hello all,

    I couldn't directly tie my question up to any of the others forums so please accept my apologies if I've posted this in the wrong location.

    I'm very new to sql coding, most of my limited DB experience is from MS Access. I'm really struggling to come up with a solution to this problem.

    I have one table called person and another called lookup. Lookup contains the text description of various codes stored in a persons record. For example if I wanted to query a persons ethnicity I would use the following code:

    SELECT person.last_name, person.first_name,lookup.description
    FROM person INNER JOIN lookup ON person.ethnicity = lookup.code
    WHERE (((lookup.code_type)=101));

    And if I wanted to query a persons nationality I use the following code:

    SELECT person.last_name, person.first_name,lookup.description
    FROM person INNER JOIN lookup ON person.nationality = lookup.code
    WHERE (((lookup.code_type)=231));

    The only difference between the two queries being the code_type criteria (used to differentiate between ethnicity and nationality codes in these cases)and where the join between the person table and the lookup.code field is(again to differentiate between ethnicity and nationality).

    If a person has a null value in person.ethnicity in the 1st example or person.nationality in the 2nd no data is returned for that record.

    I've tried using a left outer join with no success, presumably because there is criteria for the lookup.code_type field.

    Is there anyway in one sql statement I could still get data returned for records even if they have null values in the fields detailed above? I'm using this code to access data sql anywhere 11 server data on a sharepoint server.

    Please let me know if I haven't explained myself clearly enough and please excuse my crude understanding of SQL, I've been thrown in at the deep end some what by external forces.

    Many thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Use a left join and include the filter (the criteria in the where clause) in the ON predicate. In other words - swap WHERE with AND.

  3. #3
    Join Date
    May 2010
    Posts
    3
    Thank you very much for your prompt reply.

    I won't get chance to try this until tomorrow but I'll post back then.

  4. #4
    Join Date
    May 2010
    Posts
    3
    Thanks again for your reply pootle, that seems to have done the trick.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •