Seems to work fine for me:

DECLARE @Table1

TABLE

(

idno INT NULL,

id1 INT NULL,

lid1 INT NULL,

lid3 INT NULL,

name VARCHAR(20) NULL

);

DECLARE @Table2

TABLE

(

idno INT NULL,

id1 INT NULL,

lid1 INT NULL,

lid2 INT NULL,

lid3 INT NULL,

name VARCHAR(20) NULL

);

INSERT @Table1

(idno, id1, lid1, lid3, name)

VALUES (0, 1, 2, 3, 'Table 1');

INSERT @Table2

(idno, id1, lid1, lid2, lid3, name)

VALUES (0, 1, 0, 2, 3, 'Table 2');

SELECT a.IDNo,

b.IDNO,

a.Name,

b.Name

FROM @Table1 a

LEFT

OUTER

JOIN @Table2 b

ON (

a.id1 = b.id1

AND a.lid1 = b.lid2

AND a.lid3 = b.lid3

);