Post reply

Return Parent Record value from child records

  • December 18, 2013 at 9:00 am

    #286176

    I have a table that has a PK and a FK that relates to itself. See below

    For example. 7803 is the Parent Batchnumber for alll of the records that follow in the result set below

    BATCHNUMBERID ORIGINATINGBATCHID

    7803 8742FDDF-E50A-405B-8567-C69B70E1F110NULL

    7803EXP10DA8CC6E-9B51-4380-835A-331D1B34192F8742FDDF-E50A-405B-8567-C69B70E1F110

    7803EXP2F6DE105E-F8EE-40C8-AFA0-67606F7EBA7C0DA8CC6E-9B51-4380-835A-331D1B34192F

    7803EXP3A4CA5C78-8020-4F61-A3A2-E71A515497B9F6DE105E-F8EE-40C8-AFA0-67606F7EBA7C

    7803EXP4D19B2791-D0ED-4C5D-97D7-F5DDDAA475BEA4CA5C78-8020-4F61-A3A2-E71A515497B9

    7803EXP5AFB979D9-A5AD-400B-82BE-FE3142AB2BA0D19B2791-D0ED-4C5D-97D7-F5DDDAA475BE

    7803EXP623A49567-96C1-405B-82E6-50613EC3FA7EAFB979D9-A5AD-400B-82BE-FE3142AB2BA0

    7803EXP7411B5441-1EC5-44E7-A638-EA77577FC57823A49567-96C1-405B-82E6-50613EC3FA7E

    I need to create a function that will return the Parent Batchnumber (7803) if and when any of the child batchnumbers are presented. For example;

    If Batchnumber 7803EXP7 is returned then the Parent Batchnumber will be 7803, if 7803EXP6 is presented then the Parent Batch would be 7803 etc.

    I have used string functions to accomplish this, but I really need to be using the GUIDs, becuase if the naming convention is changed in the future then the string fuunctions will not work.

    Please advise.

    Thanks

  • December 18, 2013 at 9:15 am

    #1674482

    A recursive CTE can do the trick in here.

    WITH SampleData(BATCHNUMBER, ID, ORIGINATINGBATCHID) AS( SELECT

    '7803', '8742FDDF-E50A-405B-8567-C69B70E1F110', NULL UNION ALL SELECT

    '7803EXP1', '0DA8CC6E-9B51-4380-835A-331D1B34192F', '8742FDDF-E50A-405B-8567-C69B70E1F110' UNION ALL SELECT

    '7803EXP2', 'F6DE105E-F8EE-40C8-AFA0-67606F7EBA7C', '0DA8CC6E-9B51-4380-835A-331D1B34192F' UNION ALL SELECT

    '7803EXP3', 'A4CA5C78-8020-4F61-A3A2-E71A515497B9', 'F6DE105E-F8EE-40C8-AFA0-67606F7EBA7C' UNION ALL SELECT

    '7803EXP4', 'D19B2791-D0ED-4C5D-97D7-F5DDDAA475BE', 'A4CA5C78-8020-4F61-A3A2-E71A515497B9' UNION ALL SELECT

    '7803EXP5', 'AFB979D9-A5AD-400B-82BE-FE3142AB2BA0', 'D19B2791-D0ED-4C5D-97D7-F5DDDAA475BE' UNION ALL SELECT

    '7803EXP6', '23A49567-96C1-405B-82E6-50613EC3FA7E', 'AFB979D9-A5AD-400B-82BE-FE3142AB2BA0' UNION ALL SELECT

    '7803EXP7', '411B5441-1EC5-44E7-A638-EA77577FC578', '23A49567-96C1-405B-82E6-50613EC3FA7E'

    ),

    rCTE AS(

    SELECT *

    FROM SampleData

    WHERE BATCHNUMBER = '7803EXP6'

    UNION ALL

    SELECT s.*

    FROM SampleData s

    JOIN rCTE r ON s.ID = r.ORIGINATINGBATCHID

    --WHERE s.ORIGINATINGBATCHID IS NOT NULL

    )

    SELECT BATCHNUMBER

    FROM rCTE

    WHERE ORIGINATINGBATCHID IS NULL

    You can use the query to create an inLine Table-Valued Function (iTVF).

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • December 18, 2013 at 9:20 am

    #1674483

    What you have presented looks like an ugly design. The PK appears to be a string that has a 'EXPXX' suffix appended to it. Then you must apparently strip the suffix off to identify the parent.

    I'm not a proponent of having parents and children in the same table - the justification hasn't ever been made other than being able to have everything in one table for ease of export or something. But if you must do it that way, it would be far better to put have the parentID and the children be integers. Then the primary Key would be the combination of the two, a compound key. Obviously you could use Guids instead of a child ID if you wanted but would be less efficient. It really depends upon your requirements :: ie how much data is involved and what sort of transactional activity and queries will be happening on this data.

    The probability of survival is inversely proportional to the angle of arrival.

  • December 18, 2013 at 9:32 am

    #1674487

    sturner (12/18/2013)

    I'm not a proponent of having parents and children in the same table - the justification hasn't ever been made other than being able to have everything in one table for ease of export or something.

    I assume you mean when it is a simple master-detail relationship? If this has more than one level of child you pretty much need to use the same table.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • December 18, 2013 at 12:18 pm

    #1674570

    sturner (12/18/2013)

    What you have presented looks like an ugly design. The PK appears to be a string that has a 'EXPXX' suffix appended to it. Then you must apparently strip the suffix off to identify the parent.

    I'm not a proponent of having parents and children in the same table - the justification hasn't ever been made other than being able to have everything in one table for ease of export or something. But if you must do it that way, it would be far better to put have the parentID and the children be integers. Then the primary Key would be the combination of the two, a compound key. Obviously you could use Guids instead of a child ID if you wanted but would be less efficient. It really depends upon your requirements :: ie how much data is involved and what sort of transactional activity and queries will be happening on this data.

    If there's only one level of children, such as you might find in an Invoice/InvoiceDetail problem, then I absolutely agree. If it has more than 1 level of children, then I've found that having two separate tables makes life a whole lot more difficult.

    --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.

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

  • December 18, 2013 at 12:38 pm

    #1674581

    Jeff Moden (12/18/2013)

    If there's only one level of children, such as you might find in an Invoice/InvoiceDetail problem, then I absolutely agree. If it has more than 1 level of children, then I've found that having two separate tables makes life a whole lot more difficult.

    Agreed, but I was referring the OP's case and, even more specifically, the apparent concatenation of parent-child IDs into one varchar () column to produce a PK, with the attendant side effects. I have however had to deal with a situation where the data, while related more than two levels , was not physically located in the same database - actually on a different system. In cases like that you can't use the usual referential integrity mechanisms.

    The probability of survival is inversely proportional to the angle of arrival.

  • December 21, 2013 at 3:11 pm

    #1675409

    sturner (12/18/2013)

    Jeff Moden (12/18/2013)

    If there's only one level of children, such as you might find in an Invoice/InvoiceDetail problem, then I absolutely agree. If it has more than 1 level of children, then I've found that having two separate tables makes life a whole lot more difficult.

    Agreed, but I was referring the OP's case and, even more specifically, the apparent concatenation of parent-child IDs into one varchar () column to produce a PK, with the attendant side effects. I have however had to deal with a situation where the data, while related more than two levels , was not physically located in the same database - actually on a different system. In cases like that you can't use the usual referential integrity mechanisms.

    I guess I don't understand your comments here nor how they justify the need for two tables especially in an N-Level system. For example, where is the "apparent concatenation of parent-child IDs into one VARCHAR() column" to produce a PK? That absolutely not necessary to produce a PK for a single parent-child table. And since the op hasn't mentioned data being on two different databases, never mind being on two different machines, and considering that such a case is the exception rather than the rule (and still not sure why someone would do such a thing intentionally), I'm not sure why such a case was actually brought up here. I can, however, see a number of disadvantages to doing so.

    Except for guaranteed 2 level hierarchies, such as an Invoice/Invoice Detail pair, I see no advantages to splitting hierarchies into a manager and non-manager pair of tables.

    --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.

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

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

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