Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2012
    Posts
    3

    Post Unanswered: Recursive type query???

    I'm trying to produce a result set that will look like the attached doc.

    The 5 tables I'm using are- source, entity, element, relationship & relationship_detail. All the data is in the element table, but I need to show lineage in one result set. How can I go about doing this? Here is the query which helps me produce the first two columns (parent), but I need to add the corresponding child columns (the next two columns). The below query produces the first two columns, but how can I show all for using one query? Any and all help is GREATLY appreciated. Thanks for your help.

    select ent.Data_Entity_Name, elm.Data_Element_Name
    from Data_Element_Table
    inner join Data_Entity ent on elm.Data_Entity_ID = ent.Data_Entity_Id
    inner join Data_Source src on ent.Data_Source_ID = src.Data_Source_ID
    left join Relationship_Detail rdt on Data_Element_ID = rdt.Data_Element_ID and rdt.Relationship_Element_Type = 'child'
    left join Relationship rsp on rdt.Relationship_ID = rsp.Relationship_ID and Relationship_Type = 'parent'
    where src.Data_Source_Id = 113


    PLEASE HELP. Even if it can help point me in the right direction.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I don't understand your problem, nor did the Word document helped in any way.

    Can you give us some sample data for the 5 tables and the result set that you want. If you give the CREATE TABLE scripts for those 5 tables as well as INSERT statements to populate them with your sample data, you will be helped even faster.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Feb 2012
    Posts
    3

    Here is more info

    I have attached a word doc that shows the result set I am trying to get. It also has all the tables I use and sample data.

    Here is the query that shows how I connect the tables through one recursion.


    SELECT src.source_name, elm.element_name

    FROM Element_Table elm

    INNER JOIN Entity_Table ent ON elm.entity_id = ent.entity_id
    INNER JOIN Source_Table src ON ent.source_id = src.source_id
    LEFT JOIN Relationship_Detail rdt ON elm.element_id = rdt.element_id AND rdt.relationship_type = 2
    LEFT JOIN Relationship rsp ON rdt.relationship_id = rsp.relationship_id AND rsp.relatiionship_type_id = 1
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you recheck the sample data and your query?

    After running your SQL query, I first got a few syntax errors and later a different result set from yours.

    "I have 4, 5 and 2 in my database. SELECT 4 + (5 / 2) keeps giving me 6 but I need 13.5. Help!"
    It is quite difficult, based on the previous description, to give any help.

    You really need to describe your problem better. And invest some time in providing good sample data and an expected result set based on that sample data, and an explanation of the business logic behind the expected result set.

    There is also a possibility that the data model is flawed. Finding Realtionship_type_Id in the two different RELATIONSHIP tables is weird. You could also model this with columns like Parent_data_element_id and Child_data_element_id. But I may be wrong, I don't claim to understand the business logic behind the data model.

    As most people, I normally never open Word or Excel attachments (viruses).
    Can you present your sample data in the text (and NOT in a Word attachment), surrounded by [CODE]...[ /CODE] tags? as well as your SQL code. It makes reading your request a lot easier for us.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Feb 2012
    Posts
    3

    thanks for the reply

    Sorry for the confusion. I will attempt to clarify and pardon me as I'm new to the art of querying. The purpose of the query is to produce a report that will show lineage between data elements from various source systems.

    I have attached a pdf of the data model, so hopefully this will give you a better understanding of the data structures and CORRECT ID names. You were right regarding two relationship tables containing the same id. I tried to make a simple version to make it easier for others to understand, but it seemed to have the opposite effect.

    The result set I am trying to produce is fairly large, but I am also providing a query which is the starting point for producing all elements from one source. I need to show the lineage (if available) across three other source systems. I'm sure more questions will come up from my post, but I hope at least we can clear up the earlier confusion and start down the right path.

    Thanks for your help



    SELECT elm.DATA_ELEMENT_ID, elm.DATA_ENTITY_ID

    FROM DATA_ELEMENT elm

    LEFT JOIN DATA_ENTITY ent ON elm.DATA_ENTITY_ID = ent.DATA_ENTITY_ID
    LEFT JOIN DATA_SOURCE src ON ent.DATA_SOURCE_ID = src.DATA_SOURCE_ID
    LEFT JOIN RELATIONSHIP_DETAIL rdt ON elm.DATA_ELEMENT_ID = rdt.DATA_ELEMENT_ID AND rdt.RELATIONSHIP_ELEMENT_TYPE_ID = 2
    LEFT JOIN RELATIONSHIP rsp ON rdt.RELATIONSHIP_ID = rsp.RELATIONSHIP_ID AND rsp.RELATIONSHIP_TYPE_ID = 1

    where src.DATA_SOURCE_ID = 113
    Attached Files Attached Files

  6. #6
    Join Date
    Jan 2012
    Posts
    84
    Quote Originally Posted by trusharpatel99 View Post
    but I hope at least we can clear up the earlier confusion and start down the right path.
    Wim asked you to present sample in the text, surrounded by codetags, not in nice looking MS Word tables.
    I have read once a study showed that most people loved to resolve challenging problems, and usually willingly helped others, but majority of them were lazy by nature and hated to do boring tasks.
    If you do your homework and provide your sample data in a form that other can easy recreate on their computers just using a simple copy-paste, you dramatically increase chances to get help.
    But if you give people nice looking tables in Word, don't expect that many of them would be willing to tiresomely type in char by char from these tables.

    This time I prepared it for you:
    Code:
    create table Source_Table(
      SOURCE_ID int primary key,
      SOURCE_NAME varchar(100)
    );
    
    insert into Source_Table values
    ( 1 , 'Source1' ),
    ( 2 , 'Source2' ),
    ( 3 , 'Source3' ),
    ( 4 , 'Source4' ),
    ( 5 , 'Source5' );
    
    create table Entity_Table(
     ENTITY_ID int primary key,
    ENTITY_NAME varchar(100),
    SOURCE_ID int
    );
    
    insert into Entity_Table values
    ( 10, 'Entity1', 1 ),
    ( 20, 'Entity2', 1 ),
    ( 30, 'Entity3', 2 ),
    ( 40, 'Entity4', 2 ),
    ( 50, 'Entity5', 3 );
    
    create table Element_Table(
    ELEMENT_ID int primary key,
    ELEMENT_NAME varchar(100),
    DATA_ENTITY_ID int
    );
    
    insert into Element_Table values
    ( 100, 'Element1', 10 ),
    ( 200, 'Element2', 10 ),
    ( 300, 'Element3', 20 ),
    ( 400, 'Element4', 50 ),
    ( 500, 'Element5', 50 );
    
    create table Relationship_Table(
    RELATIONSHIP_ID int primary key,
    RELATIONSHIP_TYPE_ID int,
    TARGET_DATA_ELEMENT_ID int
    );
    
    insert into Relationship_Table values
    ( 65, 1, 100 ), ( 66, 1, 200 ),
    ( 67, 1, 300 ), ( 68, 1, 400 ),
    ( 69, 1, 500 );
    
    
    create table Relationship_Detail_Table(
    RELATIONSHIP_DETAIL_ID int primary key,
    RELATIONSHIP_ID int,
    DATA_ELEMENT_ID int,
    RELATIONSHIP_TYPE_ID int
    );
    
    insert into Relationship_Detail_Table values
    ( 95, 65, 100, 1 ), ( 96, 65, 400, 2 ),
    ( 97, 66, 200, 1 ), ( 98, 67, 300, 2 ),
    ( 99, 67, 500, 1 );

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    kordirko, you're spot-on.

    As long as trusharpatel99 doesn't give any logic about how the tables relate and the logic behind his expected result, I can't help him. He will have to invest some time to explain that, make some good sample data and a result set based on that sample data (the sample data and the expected result set may already be good). When he provides SQL, it should compile without any syntax errors.

    trusharpatel99, you gave us a quite complicated data model in PDF. Your first approach to only show the relevant tables, was a good choice. If the problem is located in 5 tables, we don't need information about the other 50 tables.

    If you don't give us the logic behind those 5 tables and your expected results, I can't help you. Don't forget that you are the only one on this forum who knows your situation.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •