CREATE TABLE TableA(ID int NOT NULL Primary Key,Description varchar(100));
Insert into TableA(ID,Description)values(1,'abc');
Insert into TableA(ID,Description)values(2,'xyz');
Insert into TableA(ID,Description)values(3,'asd');
Insert into TableA(ID,Description)values(4,'fgh');
Insert into TableA(ID,Description)values(5,'opo');
CREATE TABLE TableB([ID] int NOT NULL Primary Key,TableAid1 int NOT NULL ,TableAid2 int NOT NULL,TableAid3 int NOT NULL,TableAid4 int NOT NULL,
CONSTRAINT FK_tblBAid1 FOREIGN KEY (TableAid1)
REFERENCES TableA(ID),CONSTRAINT FK_tblBAid2 FOREIGN KEY (TableAid2)
REFERENCES TableA(ID),
CONSTRAINT FK_tblBAid3 FOREIGN KEY (TableAid3)
REFERENCES TableA(ID),
CONSTRAINT FK_tblBAid4 FOREIGN KEY (TableAid4)
REFERENCES TableA(ID));
Insert into TableB(ID,TableAid1,TableAid2,TableAid3,TableAid4)values(1,1,2,3,4);
Insert into TableB(ID,TableAid1,TableAid2,TableAid3,TableAid4)values(2,1,5,3,4);
SELECT ta2.Id,ta1.Dimension
FROM tableA ta1
JOIN tableB ta2 on ta1.id != ta2.id
WHERE ta2.TableAId1=ta1.Id OR ta2.TableAId2=ta1.Id OR ta2.TableAId3=ta1.Id OR ta2.TableAId3=ta1.Id
UNION
SELECT ta2.ID,ta1.Description
FROM tableA ta1
JOIN tableB ta2 on ta1.ID = ta2.ID
WHERE ta2.TableAId1=ta1.ID OR ta2.TableAId2=ta1.ID OR ta2.TableAId3=ta1.ID OR ta2.TableAId3=ta1.ID
or ta2.TableAId4=ta1.ID
The above solution is with foreign key(
SQL Fiddle[
^])