Query to get rows if value contains result of another query

• IT researcher

  SSCertifiable

  Points: 7600

  More actions

  March 5, 2013 at 5:05 am

  #1593418

  ChrisM@Work (3/5/2013)

  ---

  This is the simplest way to write it. If it generates dupes, then we'll change one side to a derived table. Can you use something a little more realistic than "entries" and "entries1", or is that what they are really called?

  SELECT *

  FROM entries e

  INNER JOIN entries1 e1

  ON e1.work LIKE e.work+'%'

  AND e1.[date] BETWEEN '01-Jan-2012' and '20-Jan-2013'

  Thanks.. that worked amazing.. I have another problem. I have a query "select * from reminder where alloted_to IN('seh','jyo','nvt',...)" and i have some values for alloted to column like 'seh\jyo','sehvt' etc..Current query does not return those rows contains 'seh\jyo','sehvt'. I know i can use "alloted_to like '%seh%' or alloted_to like '%jyo%' or ..." But if i have many values then its difficult to write 'alloted_to like' condition to all. Is there any other way?

  thanks

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 5, 2013 at 5:11 am

  #1593419

  winmansoft (3/5/2013)

  ---

  ChrisM@Work (3/5/2013)

  ---

  This is the simplest way to write it. If it generates dupes, then we'll change one side to a derived table. Can you use something a little more realistic than "entries" and "entries1", or is that what they are really called?

  SELECT *

  FROM entries e

  INNER JOIN entries1 e1

  ON e1.work LIKE e.work+'%'

  AND e1.[date] BETWEEN '01-Jan-2012' and '20-Jan-2013'

  Thanks.. that worked amazing.. I have another problem. I have a query "select * from reminder where alloted_to IN('seh','jyo','nvt',...)" and i have some values for alloted to column like 'seh\jyo','sehvt' etc..Current query does not return those rows contains 'seh\jyo','sehvt'. I know i can use "alloted_to like '%seh%' or alloted_to like '%jyo%' or ..." But if i have many values then its difficult to write 'alloted_to like' condition to all. Is there any other way?

  thanks

  Of course! Read this [/url]first, it's a great introduction. Next it would help us a lot if you could prepare the ddl and dml for the two tables.

  ^{“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

• IT researcher

  SSCertifiable

  Points: 7600

  More actions

  March 6, 2013 at 11:04 pm

  #1594284

  Thank you

  I found that i can do this

  DECLARE @Parameter VARCHAR(8000)

  SET @Parameter = 'seh,jyo,mhf'

  DECLARE @Elements TABLE

  (

  Value VARCHAR(8000)

  )

  DECLARE @N INT

  SET @Parameter = ','+@Parameter +','

  SET @N = 1

  WHILE @N < LEN(@Parameter)

  BEGIN

  INSERT INTO @Elements

  VALUES (SUBSTRING(@Parameter,@N+1,CHARINDEX(',',@Parameter,@N+1)-@N-1))

  SELECT @N = CHARINDEX(',',@Parameter,@N+1)

  END

  SELECT m.*

  FROM reminder.dbo.Reminder m,@Elements s

  where m.AllotedTo LIKE '%'+s.Value+'%'

  Anything to optimize the query?

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 7, 2013 at 1:08 am

  #1594322

  winmansoft (3/6/2013)

  ---

  Thank you

  I found that i can do this

  DECLARE @Parameter VARCHAR(8000)

  SET @Parameter = 'seh,jyo,mhf'

  DECLARE @Elements TABLE

  (

  Value VARCHAR(8000)

  )

  DECLARE @N INT

  SET @Parameter = ','+@Parameter +','

  SET @N = 1

  WHILE @N < LEN(@Parameter)

  BEGIN

  INSERT INTO @Elements

  VALUES (SUBSTRING(@Parameter,@N+1,CHARINDEX(',',@Parameter,@N+1)-@N-1))

  SELECT @N = CHARINDEX(',',@Parameter,@N+1)

  END

  SELECT m.*

  FROM reminder.dbo.Reminder m,@Elements s

  where m.AllotedTo LIKE '%'+s.Value+'%'

  Anything to optimize the query?

  Yes - do this instead:

  DECLARE @Parameter VARCHAR(8000)

  SET @Parameter = 'seh,jyo,mhf'

  SELECT *

  INTO #Elements

  FROM dbo.DelimitedSplit8K (@Parameter,',')

  SELECT m.*

  FROM reminder.dbo.Reminder m

  INNER JOIN #Elements e ON e.Item = m.AllotedTo

  Ensure that there's an index available to optimise the join. If in doubt, post the execution plan attached as a .sqlplan file.

  ^{“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

• IT researcher

  SSCertifiable

  Points: 7600

  More actions

  March 7, 2013 at 5:12 am

  #1594388

  ChrisM@Work (3/7/2013)

  ---

  yes - do this instead:

  DECLARE @Parameter VARCHAR(8000)

  SET @Parameter = 'seh,jyo,mhf'

  SELECT *

  INTO #Elements

  FROM dbo.DelimitedSplit8K (@Parameter,',')

  SELECT m.*

  FROM reminder.dbo.Reminder m

  INNER JOIN #Elements e ON e.Item = m.AllotedTo

  Ensure that there's an index available to optimise the join. If in doubt, post the execution plan attached as a .sqlplan file.

  What is dbo.DelimitedSplit8K??Its giving error-Invalid object name 'dbo.DelimitedSplit8K'.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 7, 2013 at 5:17 am

  #1594389

  winmansoft (3/7/2013)

  ---

  ChrisM@Work (3/7/2013)

  ---

  yes - do this instead:

  DECLARE @Parameter VARCHAR(8000)

  SET @Parameter = 'seh,jyo,mhf'

  SELECT *

  INTO #Elements

  FROM dbo.DelimitedSplit8K (@Parameter,',')

  SELECT m.*

  FROM reminder.dbo.Reminder m

  INNER JOIN #Elements e ON e.Item = m.AllotedTo

  Ensure that there's an index available to optimise the join. If in doubt, post the execution plan attached as a .sqlplan file.

  What is dbo.DelimitedSplit8K??Its giving error-Invalid object name 'dbo.DelimitedSplit8K'.

  It's in the article I suggested you read, about four posts back.

  ^{“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

• IT researcher

  SSCertifiable

  Points: 7600

  More actions

  March 7, 2013 at 5:46 am

  #1594396

  ChrisM@Work (3/7/2013)

  ---

  winmansoft (3/7/2013)

  ---

  ChrisM@Work (3/7/2013)

  ---

  yes - do this instead:

  DECLARE @Parameter VARCHAR(8000)

  SET @Parameter = 'seh,jyo,mhf'

  SELECT *

  INTO #Elements

  FROM dbo.DelimitedSplit8K (@Parameter,',')

  SELECT m.*

  FROM reminder.dbo.Reminder m

  INNER JOIN #Elements e ON e.Item = m.AllotedTo

  Ensure that there's an index available to optimise the join. If in doubt, post the execution plan attached as a .sqlplan file.

  What is dbo.DelimitedSplit8K??Its giving error-Invalid object name 'dbo.DelimitedSplit8K'.

  It's in the article I suggested you read, about four posts back.

  I Have created DelimitedSplit8K function and query is working..But are you sure i will get better performance from this than other one??

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 7, 2013 at 5:58 am

  #1594404

  winmansoft (3/7/2013)

  ---

  ChrisM@Work (3/7/2013)

  ---

  winmansoft (3/7/2013)

  ---

  ChrisM@Work (3/7/2013)

  ---

  yes - do this instead:

  DECLARE @Parameter VARCHAR(8000)

  SET @Parameter = 'seh,jyo,mhf'

  SELECT *

  INTO #Elements

  FROM dbo.DelimitedSplit8K (@Parameter,',')

  SELECT m.*

  FROM reminder.dbo.Reminder m

  INNER JOIN #Elements e ON e.Item = m.AllotedTo

  Ensure that there's an index available to optimise the join. If in doubt, post the execution plan attached as a .sqlplan file.

  What is dbo.DelimitedSplit8K??Its giving error-Invalid object name 'dbo.DelimitedSplit8K'.

  It's in the article I suggested you read, about four posts back.

  I Have created DelimitedSplit8K function and query is working..But are you sure i will get better performance from this than other one??

  Not without access to your data, no.

  ^{“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