Join with Same table

• sam 55243

  SSCrazy

  Points: 2697

  More actions

  August 30, 2017 at 2:08 am

  #148060

  Hi All,
   I have below scenario

  There are Two tables:

  
CREATE TABLE DBO.TBLORDERTYPE
(ID INT IDENTITY(1,1),
TYPE_DESC VARCHAR(20))

  CREATE TABLE DBO.TBLTRADES
(ID INT IDENTITY(1,1),
PRODUCTID INT,
ORDERTYPEID1 INT,
ORDERTYPEID2 INT,
ORDERTYPEID3 INT)


  Here are some sample data
  
INSERT INTO TBLORDERTYPE VALUES('MARKET'),('STOP'),('TAS'),('PROGRESS'),('HOLD'),('LIMIT'),('PLATTS')
INSERT INTO TBLTRADES (PRODUCTID, ORDERTYPEID1, ORDERTYPEID2, ORDERTYPEID3) VALUES (1, 1,2,3),(2, 2,4,5),(3, 5,6,7)


  Now i need to derive the Order Desc value for each product which i'm doing using the below query:

  SELECT T.PRODUCTID, OT1.TYPE_DESC, OT2.TYPE_DESC, OT3.TYPE_DESC FROM TBLTRADES T
LEFT JOIN TBLORDERTYPE OT1
    ON OT1.ID=T.ORDERTYPEID1
LEFT JOIN TBLORDERTYPE OT2
    ON OT2.ID=T.ORDERTYPEID2
LEFT JOIN TBLORDERTYPE OT3
    ON OT3.ID=T.ORDERTYPEID3

  Is there any efficient way (from a performance point of view) to derive the values for 'Order Desc' column without joining the same table ('tblOrderType') again and again.

  Should i go for case statement, but there are around 15 order types and the Desc value might change. Please suggest if there are any other option.

  Thanks in advance
  sam

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 30, 2017 at 2:46 am

  #1957377

  There's this:
  

  SELECT T.PRODUCTID,

  TYPE_DESC1 = MAX(CASE WHEN x.Ord = 1 THEN ot.TYPE_DESC END),

  TYPE_DESC2 = MAX(CASE WHEN x.Ord = 2 THEN ot.TYPE_DESC END),

  TYPE_DESC3 = MAX(CASE WHEN x.Ord = 3 THEN ot.TYPE_DESC END)

  FROM #TBLTRADES T

  CROSS APPLY (VALUES (1, T.ORDERTYPEID1),(2, T.ORDERTYPEID2),(3, T.ORDERTYPEID3)) x (Ord, OrderTypeID)

  LEFT JOIN #TBLORDERTYPE ot ON ot.ID = x.OrderTypeID

  GROUP BY T.PRODUCTID

  
  test it for efficiency against your existing query.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• sam 55243

  SSCrazy

  Points: 2697

  More actions

  August 30, 2017 at 5:23 am

  #1957407

  Thanks! Chris will try this out and test the performance.