• You could use correlated inline queries.

    select Col1,

    (select ColA

    from dbo.Table2

    where ColB = Table1.Col2) as ColA

    from dbo.Table1;

    This is even worse than the temp table solution, but it does fit the requirements given thus far.

    I have to say, if anyone asked me this in an interview, I'd challenge it. "Why not use Join operations?" would be my response. Probably they just like trick questions to see if you can "think outside the box", but there are much better ways to accomplish that than asking you to avoid using a standard tool of the trade.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon