Selecting alternative value if one row(possible two) doesnt exist in reference table

  • Folks, I usually had one row in table containing multiple values with nulls, which is relatively easy when comes to selectin existing value only but was wondering what will be the best approach to select one of the existing rows in reference table without writing some magic function, which I seen in post here

    I have two tables ,one

    CREATE TABLE [dbo].[ProductTable](

    [ID] [int] ,

    [ProductName] [varchar](50) ,

    [ProductDesc] [varchar](50) ,

    )

    with two products in it

    1,phone,coms

    2,mobile,coms

    and example lkp table

    CREATE TABLE [dbo].[lkpTable](

    [ID] [int] ,

    [Flag] [int] ,

    [value] [varchar](50) ,

    )

    go

    with following data

    id, flag, value

    1,2400,A

    1,1400,B

    2,1400,B

    where id is the same in both tables ProductTable.id=lkpTable.id

    each product in table will always have default temp B value with 1400 flag but may not have new allocated A value

    I was trying to select max(flag) value by matching ID but not getting any luck with it.

    idea is that if there is no value A with flag 2400 allocated , I should be selecting B value as default or say next available value for this ID

    so result should be something like this

    id, flag, value

    1,Phone,A

    2,Mobile,B

    any help appreciated , not sure how to bite this one .

  • got a brain wave , maybe there is better way of doing this but this looks like its working for me

    SELECT b.value, a.productname

    FROM b RIGHT OUTER JOIN

    a ON b.id = a.id

    where b.flag=(select max(b.flag) from b

    RIGHT OUTER JOIN

    a ON b.id = a.id)

    GROUP BY b.value, a.productname

  • This may be more efficient (most people use LEFT rather than RIGHT join, so I switched it to that):

    SELECT l.value, p.productname

    FROM dbo.ProductTable p

    LEFT OUTER JOIN (

    SELECT ID, value, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY flag DESC) AS row_num

    FROM dbo.lkpTable

    ) AS l ON l.ID = p.ID AND l.row_num = 1

    GROUP BY l.value, p.productname

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (11/27/2015)


    This may be more efficient (most people use LEFT rather than RIGHT join, so I switched it to that):

    SELECT l.value, p.productname

    FROM dbo.ProductTable p

    LEFT OUTER JOIN (

    SELECT ID, value, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY flag DESC) AS row_num

    FROM dbo.lkpTable

    ) AS l ON l.ID = p.ID AND l.row_num = 1

    GROUP BY l.value, p.productname

    thank you for suggestion .

  • Select SQL

    SELECT p.*

    , xLkp.*

    FROM dbo.ProductTable p

    CROSS APPLY -- We know table dbo.lkpTable will always have atleast one (default) row for each product in [dbo].[ProductTable]

    (

    SELECT TOP (1) *

    FROM dbo.lkpTable l

    WHERE l.ID = p.ID

    ORDER BY l.Flag DESC -- Sort descending and then pick top 1

    ) xLkp ;

    Create Test

    CREATE TABLE [dbo].[ProductTable]

    (

    [ID] [int]

    , [ProductName] [varchar](50)

    , [ProductDesc] [varchar](50)

    ) ;

    INSERT INTO [dbo].[ProductTable]

    SELECT 1, 'phone', 'coms' UNION ALL

    SELECT 2, 'mobile', 'coms' ;

    CREATE TABLE [dbo].[lkpTable]

    (

    [ID] [int]

    , [Flag] [int]

    , [value] [varchar](50)

    ) ;

    INSERT INTO [dbo].[lkpTable]

    SELECT 1, 2400, 'A' UNION ALL

    SELECT 1, 1400, 'B' UNION ALL

    SELECT 2, 1400, 'B' ;

    SELECT * FROM dbo.ProductTable ;

    SELECT * FROM dbo.lkpTable ;

  • @aditya Daruka thanks, appreciated!

  • As well as only one value is needed scalar subquery will do

    SELECT p.*,

    value = ( SELECT TOP (1) [value]

    FROM dbo.lkpTable l

    WHERE l.ID = p.ID

    ORDER BY l.Flag DESC

    )

    FROM dbo.ProductTable p

    ;

  • serg-52 (12/4/2015)


    As well as only one value is needed scalar subquery will do

    SELECT p.*,

    value = ( SELECT TOP (1) [value]

    FROM dbo.lkpTable l

    WHERE l.ID = p.ID

    ORDER BY l.Flag DESC

    )

    FROM dbo.ProductTable p

    ;

    Thx Serg-52 that's handy too

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

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