Help with a left join

[henryhenry]

HiI'm trying to join these two tables. The second one is to relate items in the first one to other items in the same table (ie apple is associated with apricot, banana, orange):Here is the primary table

+------------+---------+|			pk | item	 |+------------+---------+|			1   | apple   ||			2   | pear	||			3   | banana ||			4   | apricot ||			5   | orange |+------------+---------+

This table relates items in the first table to each other using the pk (refered to as fk_1 and fk_2)

+-----------------+--------------+--------------+| rel_pk			 |		   fk_1 |			fk_2 |+-----------------+--------------+--------------+|					1 |				1 |				3 ||					2 |				3 |				1 ||					3 |				3 |				2 ||					4 |				1 |				4 ||					5 |				1 |				5 |+-----------------+--------------+--------------+

I am trying to present the data so that if I look up a wordlike 'apple', I can get the three other words (apricot, banana, orange) that are related to it.I think it's something like a left join or union. I tried this:(SELECT item, pk, fk_1, fk_2 FROM items, rel_items WHERE pk=fk_1)UNION(SELECT item, pk, fk_1, fk_2 FROM items, rel_items WHERE pk=fk_2)ORDER BY item;But then it seems to cut out 'item' and that's what I really need to return ie:

+-----------------+--------------+--------------+| pk				  |	   word_1 |	   word_2 |+-----------------+--------------+--------------+|			   1 |			   apple |	   banana ||			   2 |			banana |		  apple ||			   3 |			banana |		   pear ||			   4 |			   apple |		apricot ||			   5 |			   apple |		orange |+-----------------+--------------+--------------+

Thanks so much for any help!

[jesh]

Hmm, perhaps something like:

SELECT rel_pk as pk, t1.item as word_1, t2.item as word_2FROM relationsTable	  INNER JOIN itemsTable t1 ON relationsTable.fk_1 = t1.pk	  INNER JOIN itemsTable t2 ON relationsTable.fk_2 = t2.pk

[henryhenry]

Hi Jesh!That works a dream! Thank you!!!Henry