Joining tables if data exists in a table

[svivian]

I have one table with a basic structure of item (varchar), type1 (int), type2 (int) and a second table of types with id (int and name (varchar). The type1 and type2 fields of the first table link to the id of the type table. There will always be one or two types; if there is only one type set the second will be 0.

 

I want to use a join to display item and the two types as text. I checked the forum and found some stuff on aliases. So far I have this query:

SELECT item.name, type1.name, type2.name
FROM item, type type1, type type2
WHERE type1.id=item.type1 AND type2.id=item.type2

 

However this only selects items where both types are set. Is there a straightforward way to fix this query? I've used conditional stuff in Oracle, which I guess probably exists in MySQL, but I'd like to avoid it if there is a simpler way.

 

Thanks to anyone who can help!

[Barand]

try

SELECT i.name, t1.name, t2.name
FROM item i
INNER JOIN type t1 ON i.type1 = t1.id
LEFT JOIN type t2 ON i.type2 = t2.id