Like and Sub Query

  • 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)

  • 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"

  • --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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply