Hello Everyone -

I'm new to DTS so I appreciate any help. So far I haven't been able to
figure this out.

I want to left join one table (TableA) to 3 other tables (TablesB, C, D) .
(I need all the data in Table A returned).

If there is a match between TableA and TableB, then I just want to return a
1 to the output set. No match, return a 0.
The same would apply if there is a match between TableA and TableC and
TableD.

My output file (or table) would consist of all of the columns from TableA, a
column with a value of 0 or 1 (for the match result with TableB), another
column with a value of 0 or 1 (for the match result with TableC) and yet
another column with a value of 0 or 1 (for the match result with TableD).

Ugh..this drives me nuts.

Thank you to anyone who can give me a clue.

Steve

select TableA.*,
case when TableB.pkid is null then 0 else 1 end,
case when TableC.pkid is null then 0 else 1 end,
case when TableD.pkid is null then 0 else 1 end
from TableA
left join TableB on (join condition)
left join TableC on (join condition)
left join TableD on (join condition)

On Tue, 8 Feb 2005 16:15:14 -0600, Steve wrote:

the query would look vaguely like this:

SELECT TableA.*,
CASE WHEN TableB.BKey IS NULL THEN 0 ELSE 1 END [MatchB],
CASE WHEN TableC.CKey IS NULL THEN 0 ELSE 1 END [MatchC],
CASE WHEN TableD.DKey IS NULL THEN 0 ELSE 1 END [MatchD]
FROM TableA
LEFT JOIN TableB ON TableA.BKey = TableB.BKey
LEFT JOIN TableC ON TableA.CKey = TableC.CKey
LEFT JOIN TableD ON TableA.DKey = TableD.DKey

Play with that in query analyzer until you have it as desired. You don't
quite say what you need to use DTS for, but I would assume that a Data
Transformation task or an Execute SQL task would do the trick from there.