Like and Sub Query

• John Burchel

  Hall of Fame

  Points: 3611

  More actions

  June 22, 2006 at 7:23 am

  #114026

  Anyone kno why this isnt working

   

  select top 10 * from sysobjects where name like '%' + (select fileformat from [dbo].[Test_table]) +  '%'

  ---

  Kindest Regards,

  John Burchel (Trainee Developer)
  

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  June 22, 2006 at 7:50 am

  #645246

  What error do you get? "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

  If table Test_table has more than one row, you will encounter problems as the error message tells you!

  Rewrite as something like following code

  select distinct top 10 so.*

  from sysobjects so,

   (

    select fileformat

    from [dbo].[Test_table]

    ) z

  where so.name like '%' + z.fileformat +  '%'

   

  IF YOUR FILEFORMAT COLUMN HAS THE FULL NAME OF THE SYSOBJECT, please remove all the %'s for speed! And rewrite as follows

  select distinct top 10 so.*

  from sysobjects so

  inner join ( 

    select fileformat

    from [dbo].[Test_table]

    )  z on so.name = z.fileformat

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• stax68

  SSChampion

  Points: 11711

  More actions

  June 22, 2006 at 10:56 am

  #645324

  --equivalently,

  select distinct top 10 so.*

  from sysobjects so

  inner join [dbo].[Test_table] t

  on so.name = t.fileformat

  --or if fileformat has low cardinality relative to table height,

  --possibly:

  select top 10 so.*

  from sysobjects so

  inner join ( 

    select distinct fileformat

    from [dbo].[Test_table]

    )  z on so.name = z.fileformat

  --but I expect the optimiser will push the 'distinct'

  --up the join order anyway if it thinks it's worth it.

  --results are indeterministic because of the unordered TOP expression

  Tim Wilkinson

  "If it doesn't work in practice, you're using the wrong theory"
  - Immanuel Kant