Need help joining two tables

[codehunter]

Hello fellow developers!


I have 2 tables that I need to join into a single table.

Table 1 'Categories' looks as following:

id | name | description
---+-------+--------------
1 | frog | small and green
2 | tiger |
3 | bear | strong but shy
4 | hawk |


And table 2 'Items' looks as (where column c1, c2 and c3 indicates a category in 'Categories' table):

id | item | c1 | c2 | c3
---+------+----+----+----
26 | aaa | 1 | 2 | 3
62 | bbb | 1 | 3 |
16 | ccc | 2 | |
27 | bbb | 3 | |
11 | fff | | |

With those two tables I want to join them into:

id | item | c1 | c2 | c3
---+------+-------+-------+------
26 | aaa | frog | tiger | bear
62 | bbb | frog | bear |
16 | ccc | tiger | |
27 | bbb | bear | |
11 | fff | | |

So in other words, I want the full content of Items table but replace the integers in column c1, c2 and c3 with the corresponding names in Categories table.

The closest thing I manage to get was by using LEFT JOIN but that only gave me 1 category:

SELECT Items.id,Items.item,Categories.name
FROM Items
LEFT JOIN Categories
ON Items.c1=Categories.id;

resulting in something like:

id | item | name
---+------+-------
26 | aaa | frog
62 | bbb | frog
16 | ccc | tiger
27 | bbb | bear
11 | fff |

And if I select more columns like "...Items.item,Categories.name,Categories.name ... ON (Items.c1=Categories.id OR Items.c2=Categories.id);" then I get duplicate rows and thats not what I want.


Additionally, what if I wanted:

id | item | c1 | desc1 | c2 | desc2 | c3 | desc3
---+------+-------+-----------------+-------+----------------+------+----------------
26 | aaa | frog | small and green | tiger | | bear | strong but shy
62 | bbb | frog | small and green | bear | strong but shy | |
16 | ccc | tiger | | | | |
27 | bbb | bear | strong but shy | | | |
11 | fff | | | | | |



Anyone got a clue on how to merge Categories and Items in these two ways???

I will use PostgreSQL v8.3 (and maybe v8.4 soon), if that can help.


Thanks in advance!
/codehunter

[triassic]

Something like this may work

SELECT Items.id,Items.item,cat1.name,cat1.description,cat2.name,cat2.description,cat3.name,cat3.description
FROM Items
LEFT JOIN Categories cat1 ON Items.c1=cat1.id
LEFT JOIN Categories cat2 ON Items.c2=cat2.id
LEFT JOIN Categories cat3 ON Items.c3=cat3.id;