As Deepak Jena wrote: "
There should be a relation between the 2 tables"!
Create another table containing ID's from both tables, for example (i use temporary tables):
CREATE TABLE #SrcCode ([SrcCodeID] INT, [SrcCodeDescription] NVARCHAR(100))
INSERT INTO #SrcCode ([SrcCodeID], [SrcCodeDescription])
VALUES(1905, 'A')
INSERT INTO #SrcCode ([SrcCodeID], [SrcCodeDescription])
VALUES(1910, 'B')
INSERT INTO #SrcCode ([SrcCodeID], [SrcCodeDescription])
VALUES(1916, 'C')
INSERT INTO #SrcCode ([SrcCodeID], [SrcCodeDescription])
VALUES(1918, 'D')
INSERT INTO #SrcCode ([SrcCodeID], [SrcCodeDescription])
VALUES(1919, 'E')
CREATE TABLE #Site ([SiteID] INT, [SiteDescription] NVARCHAR(100))
INSERT INTO #Site ([SiteID], [SiteDescription])
VALUES(56, 'ZXC')
INSERT INTO #Site ([SiteID], [SiteDescription])
VALUES(101, 'XCV')
INSERT INTO #Site ([SiteID], [SiteDescription])
VALUES(105, 'CVB')
INSERT INTO #Site ([SiteID], [SiteDescription])
VALUES(106, 'VBN')
INSERT INTO #Site ([SiteID], [SiteDescription])
VALUES(119, 'BNM')
INSERT INTO #Site ([SiteID], [SiteDescription])
VALUES(141, 'NMA')
CREATE TABLE #SrcCodeSite (SrcCodeID INT, SiteID INT)
INSERT INTO #SrcCodeSite (SrcCodeID , SiteID )
VALUES(1905, 56)
INSERT INTO #SrcCodeSite (SrcCodeID , SiteID )
VALUES(1905, 101)
INSERT INTO #SrcCodeSite (SrcCodeID , SiteID )
VALUES(1905, 105)
INSERT INTO #SrcCodeSite (SrcCodeID , SiteID )
VALUES(1905, 106)
INSERT INTO #SrcCodeSite (SrcCodeID , SiteID )
VALUES(1905, 119)
INSERT INTO #SrcCodeSite (SrcCodeID , SiteID )
VALUES(1905, 141)
SELECT *
FROM #SrcCodeSite
SELECT SC.SrcCodeID, SC.SrcCodeDescription, SI.SiteID, SI.SiteDescription
FROM #SrcCodeSite AS SCS
LEFT JOIN #SrcCode AS SC ON SCS.SrcCodeID = SC.SrcCodeID
LEFT JOIN #Site AS SI ON SCS.SiteID = SI.SiteID
DROP TABLE #SrcCode
DROP TABLE #Site
DROP TABLE #SrcCodeSite