convert JOIN into a SELECT

Question

HI,

Hope anyone can help with this question. My 3-part SQL runs in this order:

1.

INSERT INTO tblECHO SELECT * FROM qrytblXYZ

2. The query - qrytblXYZ - is this:

SELECT DISTINCT tblALPHA.one, tblALPHA.Surname AS two,  tblBRAVO.three,tblBRAVO.four
FROM tblCHARLIE
INNER JOIN tblALPHA ON tblCHARLIE.one = tblALPHA.one
INNER JOIN vwDELTA ON vwDELTA.five = tblALPHA.five
INNER JOIN tblBRAVO ON tblBRAVO.four = vwDELTA.four

it populates a temporary table - tblECHO - which is then used by:

3.

SELECT tblCHARLIE.six, tblCHARLIE.one, tblCHARLIE.[seven],
tblCHARLIE.[eight], tblCHARLIE.nine, tblECHO.two, tblCHARLIE.ten
FROM tblCHARLIE LEFT JOIN tblECHO ON tblCHARLIE.one = tblECHO.one
GROUP BY tblCHARLIE.six, tblCHARLIE.one, tblCHARLIE.[seven],
tblCHARLIE.[eight], tblCHARLIE.nine, tblECHO.two, tblCHARLIE.ten
ORDER BY tblCHARLIE.[seven];

I can no longer use the temporary table - tblECHO. So i need to make this 1 SELECT statement and NOT an INSERT statement.

Thank you for any help.

Steve

Answer 1

You could potentially use a CTE: 

http://msdn.microsoft.com/en-us/library/ms190766.aspx  check this out.