Query multiple descriptions from single field of codes

  • gadall

    Grasshopper

    Points: 12

    I have two Tables, A -- has a bunch of data I retrieve with the relevant (to this problem) field being FCode -- char(10)

    FCode can have nothing ('') or 1-3 failure codes made up of Fxx where x = number.

    There is a related table B that has

    FCode - char(3)

    FCodeDescription - char(50)

    What I need is when I query Table A I need to have the FCodeDescription (if applicable) returned inline withe the other data, the issue I'm hving is how to get the description back when there is more than one FCode. i.e. FCode in Table A = 'F02F05'.

    Any help is appreciated.

  • G.R.Prithiviraj Kulasingham

    SSCarpal Tunnel

    Points: 4104

    You need to write a function which turns the multi valued char(10) column into multi rowed table.

    Then you can join that table to TableB and get the values needed.

    I assume that you always send one column from TableA

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • LutzM

    SSC Guru

    Points: 107049

    Since the multi valued column is char(10), there can be a maximum of three values (btw: I'd rather use a char(9) length...).

    Instead of calling the function I'd probably do it inside the query.

    However, having a split string function available will help a lot if there are more columns holding multiple values (e.g. comma separated lists).

    DECLARE @t TABLE (id INT, code CHAR(10))

    INSERT INTO @t

    SELECT 1,'F02F05F03' UNION ALL

    SELECT 2,'F05F03' UNION ALL

    SELECT 3,'F02F05' UNION ALL

    SELECT 4,'F02'

    SELECT

    id,

    SUBSTRING(code,3*n+1, 3) AS code

    FROM @t

    CROSS APPLY

    (

    SELECT 0 AS n UNION ALL

    SELECT 1 UNION ALL

    SELECT 2

    ) b

    WHERE n < LEN(code)/3

    /* result set:

    id code

    1 F02

    1 F05

    1 F03

    2 F05

    2 F03

    3 F02

    3 F05

    4 F02

    */



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • ChrisM@home

    SSC-Insane

    Points: 24260

    Here's a completely different way which returns the codes in the same row.

    CREATE TABLE #A (id INT, FCode CHAR(10))

    INSERT INTO #A

    SELECT 1,'F02F05F03' UNION ALL

    SELECT 2,'F05F03' UNION ALL

    SELECT 3,'F02F05' UNION ALL

    SELECT 4,'F02'

    CREATE TABLE #B (FCode CHAR(3), FCodeDescription VARCHAR(20))

    INSERT INTO #B (FCode, FCodeDescription)

    SELECT 'F02', 'F02 description' UNION ALL

    SELECT 'F03', 'F03 description' UNION ALL

    SELECT 'F04', 'F04 description' UNION ALL

    SELECT 'F05', 'F05 description' UNION ALL

    SELECT 'F06', 'F06 description'

    SELECT a.*,

    '#' AS '#',

    b1.FCodeDescription AS Desc1,

    b2.FCodeDescription AS Desc2,

    b3.FCodeDescription AS Desc3

    FROM #A a

    LEFT JOIN #B b1 ON b1.FCode = SUBSTRING(a.FCode, 1, 3)

    LEFT JOIN #B b2 ON b2.FCode = SUBSTRING(a.FCode, 4, 3)

    LEFT JOIN #B b3 ON b3.FCode = SUBSTRING(a.FCode, 7, 3)

    Results:

    id FCode # Desc1 Desc2 Desc3

    ----------- ---------- ---- -------------------- -------------------- --------------------

    1 F02F05F03 # F02 description F05 description F03 description

    2 F05F03 # F05 description F03 description NULL

    3 F02F05 # F02 description F05 description NULL

    4 F02 # F02 description NULL NULL


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • ChrisM@home

    SSC-Insane

    Points: 24260

    There's yet another way to do this if you want an output row for each code - with OR in the join to the lookup. This isn't often recommended because the performance can be poor, so adding in this join as a last step after any preaggregation of the main query would be the way to go, something like this:

    SELECT a.*, b1.FCodeDescription

    FROM (SELECT id, FCode FROM #A WHERE 1 = 1) a

    LEFT JOIN #B b1

    ON b1.FCode = SUBSTRING(a.FCode, 1, 3)

    OR b1.FCode = SUBSTRING(a.FCode, 4, 3)

    OR b1.FCode = SUBSTRING(a.FCode, 7, 3)

    ORDER BY a.id

    Cheers

    ChrisM@home


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • gadall

    Grasshopper

    Points: 12

    This works perfectly for what I have to do.

    Thank you so much.

  • Jeff Moden

    SSC Guru

    Points: 996651

    lobbymuncher (1/24/2010)


    Here's a completely different way which returns the codes in the same row.

    CREATE TABLE #A (id INT, FCode CHAR(10))

    INSERT INTO #A

    SELECT 1,'F02F05F03' UNION ALL

    SELECT 2,'F05F03' UNION ALL

    SELECT 3,'F02F05' UNION ALL

    SELECT 4,'F02'

    CREATE TABLE #B (FCode CHAR(3), FCodeDescription VARCHAR(20))

    INSERT INTO #B (FCode, FCodeDescription)

    SELECT 'F02', 'F02 description' UNION ALL

    SELECT 'F03', 'F03 description' UNION ALL

    SELECT 'F04', 'F04 description' UNION ALL

    SELECT 'F05', 'F05 description' UNION ALL

    SELECT 'F06', 'F06 description'

    SELECT a.*,

    '#' AS '#',

    b1.FCodeDescription AS Desc1,

    b2.FCodeDescription AS Desc2,

    b3.FCodeDescription AS Desc3

    FROM #A a

    LEFT JOIN #B b1 ON b1.FCode = SUBSTRING(a.FCode, 1, 3)

    LEFT JOIN #B b2 ON b2.FCode = SUBSTRING(a.FCode, 4, 3)

    LEFT JOIN #B b3 ON b3.FCode = SUBSTRING(a.FCode, 7, 3)

    Results:

    id FCode # Desc1 Desc2 Desc3

    ----------- ---------- ---- -------------------- -------------------- --------------------

    1 F02F05F03 # F02 description F05 description F03 description

    2 F05F03 # F05 description F03 description NULL

    3 F02F05 # F02 description F05 description NULL

    4 F02 # F02 description NULL NULL

    Oh... be careful... you have to consider how hard the I/O system needs to work when doing multiple self joins. It's a very common performance problem.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996651

    gadall (1/24/2010)


    This works perfectly for what I have to do.

    Thank you so much.

    Heh... 3 solutions were given. Which one did you use?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • gadall

    Grasshopper

    Points: 12

    The one with the 3 self joins.

  • ChrisM@Work

    SSC Guru

    Points: 186107

    Jeff Moden (1/24/2010)


    lobbymuncher (1/24/2010)


    Here's a completely different way which returns the codes in the same row.

    Oh... be careful... you have to consider how hard the I/O system needs to work when doing multiple self joins. It's a very common performance problem.

    Not near as bad as those non-sargable SUBSTRINGs ๐Ÿ˜‰

    Wonder if SUBSTRING(column, 1 ... is actually SARGable? Will attempt a test if time permits.

    [font="Arial"]โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

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

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