Join two tables in sql server

Question

I have to fetch a value from two tables as a single result. But I am unable to join it. Please help me.

 

select Constituent_Indices from ListingInfo A where comp_code='C0000578' --tbl one

--table two
DECLARE @index_name VARCHAR(500)
SELECT @index_name = COALESCE(@index_name + ', ', '') + index_name
FROM MasterTables..indices_master B where comp_code='C0000578'
SELECT LEFT(RTRIM(@index_name), ( LEN(RTRIM(@index_name)))) as Constituent_Indices