Query

• Prakash-485822

  SSC Eights!

  Points: 982

  More actions

  June 7, 2008 at 3:02 am

  #71434

  Dear All,

  I have 2 table

  The tab1 table contains the following data

  PrimaryId FormCode Datecreated Recipientid

  1 CHR-1 20/12/2008 A

  2 Issue-2 20/12/2008 A

  3 CHR-3 20/12/2008 A

  4 Sup-1 20/12/2008 A

  5 Sup-2 20/12/2008 A

  the tab2 tab contains the following data

  Id ProfileId FormCodeName Action

  1 CHR CHR,Issue ListCHR

  2 Sup Sup ListSup

  3 ERR ERR ListERR

  i am using the following query to retrive data

  the substring of formcode in the tab1 should match with the comma delimated values of column formnamecode in tab2

  select * from tabl1

  where -AND SubString(Formcode,0,CharIndex('-',Formcode))=tab2.FormNamecode

  I want the output in the following way

  PrimaryId FormCode Datecreated Recipientid profileId

  1 CHR-1 20/12/2008 A CHR

  2 Issue-2 20/12/2008 A Issue

  3 CHR-3 20/12/2008 A CHR

  4 Sup-1 20/12/2008 A Sup

  5 Sup-2 20/12/2008 A Sup

  Is the above query returns this output?

  Please help me to solve this.

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  June 7, 2008 at 1:33 pm

  #825829

  Please read the article at the URL in my signature before you post again. You're post should have looked like the following...

  --===== Setup the test table

  SET DATEFORMAT DMY

  DROP TABLE #Test

  GO

  CREATE TABLE #Test

  (PrimaryId INT, FormCode VARCHAR(10), Datecreated DATETIME, Recipientid CHAR(1))

  INSERT INTO #Test

  (PrimaryId, FormCode, Datecreated, Recipientid)

  SELECT'1','CHR-1','20/12/2008','A' UNION ALL

  SELECT'2','Issue-2','20/12/2008','A' UNION ALL

  SELECT'3','CHR-3','20/12/2008','A' UNION ALL

  SELECT'4','Sup-1','20/12/2008','A' UNION ALL

  SELECT'5','Sup-2','20/12/2008','A'

  ... so that we could concentrate on your problem, like this (no need to join to the other table according to the output you requested.)

  --===== Do the problem

  SELECT *,

  LEFT(FormCode,CHARINDEX('-',FormCode)-1)

  FROM #Test

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions