Need help in writing this query..plz help

• Prasad R

  Grasshopper

  Points: 17

  More actions

  February 3, 2005 at 11:32 pm

  #163185

  I need to write a query that does the following:

  There are three tables, say T1,T2 and T3. All these tables have three fields in common K1,K2 and K3. I need to set the value of a status flag in the result set depending on the following conditionthis status flag is not present in any table but should be present in the output data)

  If, for the combinations K1,K2 and K3, there are no rows in T3 and the value of a column(say C1) in T1 is 'TC', then status flag should be set to 'N'

  else if, for the combinations K1,K2 and K3, there is atleast one row in T3 and the value of a column(say C1) in T1 is 'TC', then status flag should be set to 'D'.

  Can anyone plz help me..

   

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  February 4, 2005 at 9:52 am

  #540726

  I am not sure how you are including table T2 in this by your Business Rules. 

  I have put together this little script; maybe you can give me more information. 

   

  CREATE TABLE #T1( K1 int,

    K2 int,

    K3 int, 

    C1 varchar(10))

  CREATE TABLE #T2( K1 int,

    K2 int,

    K3 int )

  CREATE TABLE #T3( K1 int,

    K2 int,

    K3 int )

  INSERT INTO #T1  SELECT 1, 2, 3, 'TC'

  INSERT INTO #T1  SELECT 4, 5, 6, 'TC'

  INSERT INTO #T1  SELECT 7, 8, 9, 'TC'

  INSERT INTO #T2  SELECT 1, 2, 3

  INSERT INTO #T2  SELECT 4, 5, 6

  INSERT INTO #T3  SELECT 1, 2, 3

  INSERT INTO #T3  SELECT 4, 5, 6

  SELECT #T1.K1, #T1.K2, #T1.K3, #T1.C1,

   #T3.K1, #T3.K2, #T3.K3,

   CASE WHEN #T3.K1 IS NULL THEN 'N' ELSE 'D' END

  FROM #T1

       LEFT JOIN #T3 ON( #T1.K1 = #T3.K1 

                           AND #T1.K2 = #T3.K2

                           AND #T1.K3 = #T3.K3) 

  DROP TABLE #T1

  DROP TABLE #T2

  DROP TABLE #T3

  I wasn't born stupid - I had to study.