Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40

    Unanswered: Selecting from a join that may not have a value in foreign key

    Hey all,

    I'm trying to write an SQL query for an application i'm writing to query a relational database.
    The tables i'm trying to query contain foreign keys to other tables and these fields are optional.
    my problem.....
    Take the following tables as a simplified example
    ------------------
    [TABLE1]
    [PK] ID
    Description
    Date
    [FK] Type_ID
    -----------------
    [TABLE2]
    [PK] Type_ID
    Type_Description
    -----------------

    Now the field "Type_ID" is optional in the first table and so by default is NULL.
    So when I want to write a query that returns the related data stored in both tables that looks like this....
    SELECT * FROM TABLE1, TABLE2 WHERE TABLE1.Type_ID = TABLE2.Type_ID and TABLE1.ID = @inputParam

    But if the FK in TABLE1 is NULL then it will return no rows.

    Is there a SQL Server SQL function that I can use to conquer this?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it's called a LEFT OUTER JOIN

    SELECT *
    FROM TABLE1 left outer join TABLE2 on TABLE1.Type_ID = TABLE2.Type_ID
    where TABLE1.ID = @inputParam
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40

    Unhappy

    Oh right... thanks :$
    Every time I'm making progress in my database education... someone shows me I got to master the basics yet

  4. #4
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40
    Question.... can you have multiple outer left joins in a query that contains 3 tables for example?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    certainly!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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