How do I get this SELECT statement working?

lizardman · #1 (**permalink**) 11-27-08, 09:27

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!