Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008
    Posts
    2

    Unanswered: How do I get this SELECT statement working?

    Hi,

    I'm fairly new to SQL, at least to the more complex statements. I'm not really sure how to call the problem I got but please let me explain ...

    I got several tables in my (sqlite) Db, some of them contain just lists of names that may be related to in other tables. For example:

    companies table:
    Code:
    id    INTEGER
    name  STRING
    Now in another table there 'might' be a reference to an id in the companies table but it can also be NULL. e.g. ...

    products table:
    Code:
    id           INTEGER
    name         STRING
    publisherID  INTEGER
    developerID  INTEGER
    In the products table entries might have a publisherID or a developerID or even both or they can also both be NULL.

    What I'm trying to get with SELECT is a row of a single product that contains all the information from the products table plus the name of the publisher and developer (if any). e.g. ...

    Code:
    id     name     publisher      developer
    -------------------------------------------
    0      Foo      companyX       companyY
    ... or if there is no publisher id for an item in the product table ...

    Code:
    id     name     publisher      developer
    -------------------------------------------
    22     Toast    NULL           companyXY

    Is there a way to write a SELECT statement that could filter like that or is my approach even elementally wrong here? I've tried several approaches but it ever only lists entries if there is any id for companies in the products table. I haven't found any solution anywhere else so far, not even in some books. I'd appreciate any help!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at the JOIN verb, using a LEFT or RIGHT JOIN as required
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    always use LEFT OUTER JOIN, always shun RIGHT OUTER JOIN

    and healdem, he's using SQLite, not MySQL (i've moved the thread)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2008
    Posts
    2
    So Left Outer Joins are the way to go. Thanks for pointing into the right direction! I get it working now!

Posting Permissions

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