Order CodeDes1      CodeDes2
12345 Description3  Description5
23456 Description2  Description4

Thanks for your help

Table 1
OrderID    Code1,   Code2
12345       3        5
23456       2        4

Table 2
Code#     CodeDescription
1         Description1
2         Description2
3         Description3
4         Description4
5         Description5

DECLARE @Table1 TABLE ( OrderID INT , Code1 INT,Code2 INT)

INSERT INTO @Table1
SELECT 12345,3,5 UNION ALL
SELECT 23456,2,4

DECLARE @Table2 TABLE ( Code#  INT , CodeDescription VARCHAR(100))

INSERT INTO @Table2
SELECT  1, 'Description1' UNION ALL
SELECT  2, 'Description2' UNION ALL
SELECT  3, 'Description3' UNION ALL
SELECT  4, 'Description4' UNION ALL
SELECT  5,  'Description5'

SELECT orderID AS 'Order',t2.CodeDescription AS 'CodeDes1',t3.CodeDescription AS 'CodeDes2'
FROM @Table1 t1
INNER JOIN @Table2 t2 ON t2.Code# = t1.Code1
INNER JOIN @Table2 t3 ON t3.Code# = t1.Code2
ORDER BY orderID

quote:

---

Originally posted by mymatrix

Try this..

Select OrderId 'Order',
'CodeDesc1' = (select CodeDescription from Table2 where Code#=Table1.Code1),
'CodeDesc2' = (select CodeDescription from Table2 where Code#=Table1.Code2)

from Table1



thnks
Gaurav

Even my blood group says be -ve to all the negatives.

---