t-SQL help..

• tran008

  Ten Centuries

  Points: 1064

  More actions

  April 8, 2025 at 1:31 pm

  #4575209

  I have the following...anyone can point me the right direction to rewrite this code?

   

   

   

  IF Object_id('tempdb..#TMP1') IS NOT NULL

  DROP TABLE #TMP1

  IF Object_id('tempdb..#TMP2') IS NOT NULL

  DROP TABLE #TMP2

  SELECT * INTO #TMP1 FROM (

  SELECT 1251470 XID,       1569175 TRX,       59164   CID UNION ALL

  SELECT 1251470,       1569175,       59416 UNION ALL

  SELECT 1251470,       1569175,       59517 UNION ALL

  SELECT 1251470,       1569175,       59618 UNION ALL

  SELECT 1251470,       1569175,       59419 UNION ALL

  SELECT 1251470,       1569175,       59530 UNION ALL

  SELECT 1251470,       1569175,       59638 UNION ALL

  SELECT 1251470,       1569175,       59680 )X

  SELECT * INTO #TMP2 FROM (

  SELECT 1251470 ID, 59164 CID, 59418 LINEX UNION ALL

  SELECT 1251470 ,       59418 ,       59517 UNION ALL

  SELECT 1251470 ,       59517 ,       59530 UNION ALL

  SELECT 1251470 ,       59517 ,       59419 UNION ALL

  SELECT 1251470 ,       59530 ,       59680)Y

   

  select  t2.ID, t2.LINEX, t1.*  from #tmp1 t1

  LEFT JOIN #tmp2  t2

  ON  t1.XID = t2.ID

  AND t1.CID= t2.CID

  where t1.CID in (select LINEX from #tmp2) /***Is there better way to rewrite this? Seem like unnecessary step to read the table again***/

  and ID is null

  thxs.

• ScottPletcher

  SSC Guru

  Points: 101187

  More actions

  April 8, 2025 at 2:58 pm

  #4575294

  I think you might have to read the table again to do what you need to do.  Probably best to just do an INNER JOIN, though:

  select  t2.ID, t2.LINEX, t1.*

  from #tmp1 t1

  INNER JOIN #tmp2 t2i

  ON t2i.LINEX = t1.CID

  LEFT JOIN #tmp2  t2

  ON  t1.XID = t2.ID

  AND t1.CID= t2.CID

  WHERE t2.ID is null

   

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".