Post reply

Need Help with Query

  • August 16, 2006 at 3:15 pm

    #191949

    Here are sample data:

    create table #Item

    (I_ID int identity(1,1)

    ,ItemName varchar (100)

    ,Active bit)

    create table #Commission

    (I_ID varchar(100),

    Commission varchar(10))

    insert #Item (ItemName, Active) values ('A', 1)

    insert #Item (ItemName, Active) values ('B', 1)

    insert #Item (ItemName, Active) values ('C', 0)

    insert #Item (ItemName, Active) values ('D', 1)

    insert #Item (ItemName, Active) values ('E', 0)

    insert #Commission (I_ID, Commission) values (',1','15.0')

    insert #Commission (I_ID, Commission) values (',2,4','12.5')

    insert #Commission (I_ID, Commission) values (',3', '10')

    insert #Commission (I_ID, Commission) values (',5', '11')

    Now I would like to select the rows in #Commission table that have I_ID matching active I_ID in #Item table.  The result should be the first two rows in #Commission table where I_ID = 1, 2, and 4.  I've tried but couldn't get the expected result.  Thanks.

    Don't ask me about the weird table design.  That is a long story.

     

     

  • August 17, 2006 at 12:17 am

    #655372

    It's messy, inefficient and I'm sure several others will have better solutions, but this does what you want. The big problem is the comma-delimited values in the commission table

    SELECT

    DISTINCT C.* FROM #item I inner JOIN #commission C ON C.I_ID LIKE '%' + CAST(I.I_ID AS VARCHAR(4)) + '%'

    WHERE I.Active=1

    The distinct is necessary since the second line in commission matches 2 lines in items, and hence is returned twice.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 17, 2006 at 1:16 am

    #655380

    Gila, you are almost there! Just make sure you don't match 4 and 14 and so on with LIKE %.

    declare @item table (i_id int identity(1,1), itemname varchar (100), active bit)

    insert @item

    select 'A', 1 union all

    select 'B', 1 union all

    select 'C', 0 union all

    select 'D', 1 union all

    select 'E', 0

    declare @commission table (i_id varchar(100), commission varchar(10))

    insert @commission

    select ',1', '15.0' union all

    select ',2,4', '12.5' union all

    select ',3', '10' union all

    select ',5', '11'

    select distinct c.i_id,

                    c.commission

    from            @commission c

    inner join      @item i on PATINDEX('%,' + CONVERT(VARCHAR, i.i_id) + ',%', ',' + c.i_id + ',') > 0

    where           i.active = 1

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

  • August 17, 2006 at 2:00 am

    #655384

    Select i.I_ID,ItemName,Active,Commission

    From #item I INNER JOIN #commission C

    on CHARINDEX(RTRIM(CAST(I.I_ID as char(4))),c.I_ID)>0

    Where I.active=1

    Kindest Regards,

    Hari

  • August 17, 2006 at 2:03 am

    #655385

    This one is more accurate >>

    Select i.I_ID,ItemName,Active,Commission

    From #item I INNER JOIN #commission C

    on CHARINDEX(','+RTRIM(CAST(I.I_ID as char(4))),c.I_ID)>0

    Where I.active=1

    Kindest Regards,

    Hari

  • August 17, 2006 at 12:48 pm

    #655522

    thanks guys.

     

  • August 17, 2006 at 11:44 pm

    #655568

    Oops, missed that. Thanks

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 7 (of 7 total)

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