locked
return a record every time sql RRS feed

  • Question

  • i want read a record by table every time, number of input values may vary from 1 to n.
    multi-valued fields in the input table should be apart and separate searches

            Input table with one row (multi-valued fields), Value  (nvarchar(500))
            ------
            Ann
            John,Tom
            Mark
            ...

    user table: i want search any record of input table (The content of the row is unknown) in user table and return exists content

        user id  |   name
         12            Tom
         132           Ann
         32            Dina
         2             Mark   

    Output#1:

        user id  |   name
         12            Tom
         132           Ann
         2             Mark 

    **input and user Tables the number of rows Not fixed.
    The content of the input table row is unknown, Is there anyone to help me?**
    • Edited by Qrosh Wednesday, December 5, 2012 9:50 AM not complate
    • Changed type Naomi N Friday, December 7, 2012 3:53 AM Question rather than discussion
    Wednesday, December 5, 2012 8:37 AM

Answers

  • Here you go

    create table #input (val nvarchar(20))

    insert into #input values ('Ann')
    insert into #input values ('John,Tom')
    insert into #input values ('Mark')

    create table #user (id int,val nvarchar(20))
    insert into #user values (12,'Tom')
    insert into #user values (132,'Ann')
    insert into #user values (32,'Dina')
    insert into #user values (2,'Mark')



    SELECT IDENTITY(INT) "n" INTO Numbers
      FROM sysobjects s1
     CROSS JOIN sysobjects s2
     GO

    WITH cte
    AS
    (
    SELECT SUBSTRING(val, n, CHARINDEX(',', val + ',', n) - n) AS Val
    from #input,numbers where substring(','+val,n,1)=','
    AND n < LEN(val) + 1
    ) SELECT * FROM cte JOIN #user ON cte.val=#user.val


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Marked as answer by Iric Wen Thursday, December 13, 2012 9:56 AM
    Wednesday, December 5, 2012 12:04 PM
    Answerer

All replies

  • >>>>i want read a record by table every time

    I am not sure that understand your question......

    SELECT Value,ROW_NUMBER() OVER (ORDER BY Value) rn 

    FROM tb

    Or perhaps  you need  using TOP clause

    DECLARE @read_record INT =2

    SELECT TOP(@read_record) value FROM tbl ORDER BY Value


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, December 5, 2012 8:44 AM
    Answerer
  • Hi Qrosh,

    First you have to create a table type for inserting your input table

    @MyTable dbo.tvp_MyTable READONLY

    select userid, name from user

    where name in (select name from @MyTable)

    Wednesday, December 5, 2012 11:56 AM
  • Here you go

    create table #input (val nvarchar(20))

    insert into #input values ('Ann')
    insert into #input values ('John,Tom')
    insert into #input values ('Mark')

    create table #user (id int,val nvarchar(20))
    insert into #user values (12,'Tom')
    insert into #user values (132,'Ann')
    insert into #user values (32,'Dina')
    insert into #user values (2,'Mark')



    SELECT IDENTITY(INT) "n" INTO Numbers
      FROM sysobjects s1
     CROSS JOIN sysobjects s2
     GO

    WITH cte
    AS
    (
    SELECT SUBSTRING(val, n, CHARINDEX(',', val + ',', n) - n) AS Val
    from #input,numbers where substring(','+val,n,1)=','
    AND n < LEN(val) + 1
    ) SELECT * FROM cte JOIN #user ON cte.val=#user.val


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Marked as answer by Iric Wen Thursday, December 13, 2012 9:56 AM
    Wednesday, December 5, 2012 12:04 PM
    Answerer