I have 2 tables. 1 Item table & 2 Category table.

I need a query which returns the cat_Code, Cat_Desc and Cat_DescShor data from the category table where the value in the Item_cat 1,2 & 3 columns in the item table matches the Cat_ID in the category table.

item table

Category table

So in this instance the query will return 3 rows.

1. 123456, Jumper, Adult, Adult, Adult Unisex
2. 123456, Jumper, ACAS, Adult Casual, Adult Cas
3. 123456, Jumper, ADULTJ, Adult Jumper, Adult Jump

The query i have which obviously isn’t working

select it.item_code, it.item_desc, ct.Cat_Code, ct.Cat_Desc, ct.Cat_Desc_Short
from item it, category ct
where it.item_cat_1 =  ct.Cat_ID
and it.item_cat_2 =  ct.Cat_ID
and it.item_cat_3 =  ct.Cat_ID
and Item_Code = '123456'

The query above returns no results but when I remove the additional ‘and’ clauses on the it.item_cat columns I get a single row.

select it.item_code, it.item_desc, ct.Cat_Code, ct.Cat_Desc, ct.Cat_Desc_Short
from item it, category ct
where it.item_cat_1 =  ct.Cat_ID
and Item_Code = '123456'

123456, Jumper, Adult, Adult, Adult Unisex

I have tried the various join options but not managed to get the results I am looking for.
Any suggestions are greatly appreciated.

>Solution :

You can use this query for your problem:

SELECT it.item_code, it.item_desc, 
       ct1.Cat_Code, ct1.Cat_Desc, ct1.Cat_Desc_Short,
       ct2.Cat_Code, ct2.Cat_Desc, ct2.Cat_Desc_Short,
       ct3.Cat_Code, ct3.Cat_Desc, ct3.Cat_Desc_Short
FROM item it
JOIN category ct1 ON it.item_cat_1 = ct1.Cat_ID
JOIN category ct2 ON it.item_cat_2 = ct2.Cat_ID
JOIN category ct3 ON it.item_cat_3 = ct3.Cat_ID
WHERE it.Item_Code = '123456';

This query uses three separate joins to link the item table with the category table based on the values in the item_cat columns. It then selects the required columns from both tables for each matching row. Finally, the WHERE clause filters the results to only include the rows with the specified item code.