select rows based on code

  • hi

    i have 1 table

    code item1

    a pen

    a pencil

    b item3

    b item4

    i need to select any item based on code in just 1 column.

    now item3 contains pen and pencil.

    so i supply @item3 it gives me

    a pen

    a pencil

    ,what i need is ,if @sign is there,it should return b item3

    how to do that?

  • i read your post twice, and I cannot visualize it;

    is this your what your data would look like?

    CREATE TABLE [dbo].[MYTABLE] (

    VARCHAR(10) NULL,

    [ITEM1] VARCHAR(30) NULL)

    INSERT INTO [dbo].[MYTABLE]

    SELECT 'a','pen' UNION ALL

    SELECT 'a','pencil' UNION ALL

    SELECT 'b','item3' UNION ALL

    SELECT 'b','item4'

    what do you mean 'item3' contains a pen and a pendcil?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • no,

    this is select statement in my sp,

    i need to supply one parameter @name

    this contains 2 item suppose pen and pencil,

    and it gives me that 2 item ,

    now in table w have values like this

    a pen

    a pencil

    b name

    so ,i dont want the items it contains,i want result as (b , name) when i supply @ in sp

  • hbtkp (4/11/2012)


    no,

    this is select statement in my sp,

    i need to supply one parameter @name

    this contains 2 item suppose pen and pencil,

    and it gives me that 2 item ,

    now in table w have values like this

    a pen

    a pencil

    b name

    so ,i dont want the items it contains,i want result as (b , name) when i supply @ in sp

    Details.

    Remember noone here is looking over your shoulder, seeing what you see;

    show us exactly hat you are trying to do, no pseudo code, and we can help better.

    so now i'm thinking your question is like this:

    EXECUTE sp_mySearch 'pen,pencil,marker'

    and you are asking how to turn the comma delimited parameter into a table, so you cna search?

    I'f i'm on target, search for the DelimitedSplit8K function.

    to get the results, it's very simple:

    SELECT *

    FROM MyTable

    WHERE myColumn IN (SELECT Item

    FROM dbo.DelimitedSplit8K(@myParameter,',') myf

    )

    again, if you need more help, you have got to provide details; i'm just doing a best guess based on experience here.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • ok.

    le tme give you example.

    exec spname

    @name = @item3

    this is how i need to execute my sp.

    now @item3 is a group , it contains pen and pencil

    in my table i have values like this

    1 pen

    1 pencil

    2 item3

    so , for group it has code 2 for single it has code 1.

    when i execute @item3

    it gives me whatever it has inside ,like 1 pen

    1 pencil

    i want third row 2 ,item3 as a my result

    i hope you get it

  • i didn't even read your reply.

    you have got to do your part first.

    help us help you!

    1. provide a CREATE TABLE example representative of what you are using.

    2. provide INSERT INTO that table with some sample data(i did it in an earlier post, it's not hard!)

    3. show us the EXACT code you have tried so far.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • hbtkp (4/11/2012)


    ok.

    le tme give you example.

    exec spname

    @name = @item3

    this is how i need to execute my sp.

    now @item3 is a group , it contains pen and pencil

    in my table i have values like this

    1 pen

    1 pencil

    2 item3

    so , for group it has code 2 for single it has code 1.

    when i execute @item3

    it gives me whatever it has inside ,like 1 pen

    1 pencil

    i want third row 2 ,item3 as a my result

    i hope you get it

    Actually, no. How do we know what Item3 contains? Nothing you have provided shows us this relationship.

  • You say that you are supplying one parameter, but you don't say what value you're supplying to that parameter.

    You say that name contains pen and pencil, but the data provided does not support assertion. There is no relation between pen, pencil, and name in the data provided.

    You mention that you supply @, but that is an invalid variable/parameter name. If you mean that you are supplying '@', you need to indicate that it is a string by providing the single quotes.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • @ stands for group

    i have group name item3 ,which i am supplying in my sp.

    now this group contains pen ,pencil .so in my table i have code for everything

    for pen,pencil and item3

    i want only item3 code should be getting when i run query

  • hbtkp (4/11/2012)


    @ stands for group

    i have group name item3 ,which i am supplying in my sp.

    now this group contains pen ,pencil .so in my table i have code for everything

    for pen,pencil and item3

    i want only item3 code should be getting when i run query

    But nothing you have shown us tells us that pen and pencil are part of Item3.

  • hbtkp (4/11/2012)


    @ stands for group

    No, @ stands for the beginning of a variable/parameter definition. '@' may stand for group, but not @.

    i have group name item3 ,which i am supplying in my sp.

    now this group contains pen ,pencil .so in my table i have code for everything

    No, you don't have everything. You are missing the relationship. You table has three completely unrelated items.

    The ReadMyMind API is still vaporware, so the database has no way of knowing what's related unless you specifically tell it, and you haven't yet provided that information.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • assume that it is part of item 3 ,it is not in database ,it is in application

  • hbtkp (4/11/2012)


    assume that it is part of item 3 ,it is not in database ,it is in application

    How is the database supposed to know that Item3 is composed of pen and pencil? It isn't clairvoyant, it has to be told.

  • it doesnt matter , how do i get those value in select stm

  • hbtkp (4/11/2012)


    it doesnt matter , how do i get those value in select stm

    lol . if it doesn't matter, well...whatever.

    I'll ask again for some specific code: show us what you've tried, even if it's not working.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 15 posts - 1 through 15 (of 24 total)

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