Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2007
    Location
    Grand Rapids, MI
    Posts
    7

    Unanswered: Parent-Child View without using Cursors

    I would like to create a View (we'll call it FamilyView) using two tables, that I can then query against.
    For example:

    Parent
    {
    ID_PK,
    Name,
    PhoneNum,
    Address
    }

    Child
    {
    ID_PK,
    ParentID_FK,
    Name
    }

    The view would return a dataset like this:

    Parent.Name, Parent.PhoneNum, Parent.Address, Child.Name1, Child.Name2, Child.Name3... Child.NameN

    William Smith, (555)555-5555, 123 Main Street, Susie, Peter, Bill Jr, Fred
    Jason Jones, (666)666-6666, 54332 South Ave, Brian, Steven
    Kay McPeak, (777)777-7777, 9876 Division NW, Kathy, Sally, Karen, Deb, Becky, Kendra, Ann, Edward


    with an unknown number of children for each parent.

    Then I would like to be able to query against this view with something like this:

    SELECT * FROM FamilyView Where Child2 = 'Peter'


    I have no idea how to write the SQL for this View. Is it possible?
    Is this possible without using a cursor?

    Thanks for any advice you all can give me.
    Brian

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What Version of SQL Server?

    If it's 2005, you can use CTE (Common Table Expreassions)

    If it's 2000, you probably need to use a udf that returns a table
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2007
    Location
    Grand Rapids, MI
    Posts
    7
    It's SQL Server 2000.

    Could you give me an example of how a UDF would be used to solve this please?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    So you want to find where the family tree for a child somewhere in the middle?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2007
    Location
    Grand Rapids, MI
    Posts
    7
    I want to display each "family" in a single row in a result set and then be able to filter those families where the second child listed is 'Peter' (for example) and view only the families where Peter is the name of the second child. Does that make sense?

    My real problem is a little more complex, but I thought that if I used this example it would eliminate a lot of explanation of the problem domain.
    Last edited by brianmhowell; 07-26-07 at 14:49.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Here you go, either a sproc, or a udf for set based stuff

    Code:
    CREATE TABLE Parent (
    	  ID_PK int IDENTITY(1,1)
    	, [Name] varchar(20)
    	, PhoneNum varchar(20)
    	, Address varchar(30))
    
    CREATE TABLE Child (
    	  ID_PK int
    	, ParentID_FK int)
    GO
    
    INSERT INTO Parent([Name],PhoneNum, Address)
    SELECT 'Annie',     '111-111-1111', '1st Street' UNION ALL
    SELECT 'Bob',       '222-222-2222', '2nd Street' UNION ALL
    SELECT 'Cathy',     '333-333-3333', '3rd Street' UNION ALL
    SELECT 'Don',       '444-444-4444', '4th Street' UNION ALL
    SELECT 'Emily',     '555-555-5555', '5th Street' UNION ALL
    SELECT 'Frank',     '666-666-6666', '6th Street' UNION ALL
    SELECT 'Georgette', '777-777-7777', '7th Street' UNION ALL
    SELECT 'Harry',     '888-888-8888', '8th Street'
    
    INSERT INTO Child(ID_PK, ParentID_FK)
    SELECT 1, null UNION ALL
    SELECT 2, 1    UNION ALL
    SELECT 3, 2    UNION ALL
    SELECT 4, 3    UNION ALL
    SELECT 5, null UNION ALL
    SELECT 6, 5    UNION ALL
    SELECT 7, 6    UNION ALL
    SELECT 8, 7
    GO
    
    SELECT * FROM Parent p LEFT JOIN Child c ON p.ID_PK = c.ID_PK
    GO
    
    
    CREATE FUNCTION udf_FindTree (@Child varchar(20))
    RETURNS varchar(8000)
    AS
    BEGIN
    DECLARE @p int, @p_save int, @rs varchar(8000)
    SELECT @p = 0, @p_save = 0
    SELECT @p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK 
     WHERE [Name] = @Child
    --Loop Until @@rowcount = 0
    WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @p)
      BEGIN
    	SELECT @p_save = @p
    	SELECT @p = ParentID_FK FROM Child c WHERE ID_PK = @p_save
    -- The Last assignement is the top Parent
      END
    --Now Walk from the top Down until @@rowcount = 0
    SELECT @p = @p_save
    SELECT @rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @p
    WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @p)
      BEGIN
    	SELECT @rs = @rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @p
    	SELECT @p = ID_PK FROM Child WHERE ParentID_FK = @p
      END
    RETURN @rs
    END
    GO
    
    SELECT dbo.udf_FindTree('Cathy')
    GO
    
    SELECT * FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK 
     WHERE [Name] = 'Cathy'
    GO
    
    CREATE PROC usp_FindTree @Child varchar(20)
    AS
    SET NOCOUNT ON
    DECLARE @p int, @p_save int, @rs varchar(8000)
    SELECT @p = 0, @p_save = 0
    SELECT @p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK 
     WHERE [Name] = @Child
    --Loop Until @@rowcount = 0
    WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @p)
      BEGIN
    	SELECT @p_save = @p
    	SELECT @p = ParentID_FK FROM Child c WHERE ID_PK = @p_save
    -- The Last assignement is the top Parent
      END
    --Now Walk from the top Down until @@rowcount = 0
    SELECT @p = @p_save
    SELECT @rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @p
    WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @p)
      BEGIN
    	SELECT @rs = @rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @p
    	SELECT @p = ID_PK FROM Child WHERE ParentID_FK = @p
      END
    SELECT @rs AS rs
    SET NOCOUNT OFF
    GO
    
    EXEC usp_FindTree 'Cathy'
    GO
    
    DROP PROC usp_FindTree
    DROP Function udf_FindTree
    DROP TABLE Parent, Child
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You could even do

    SELECT DISTINCT dbo.udf_FindTree([name]) FROM Parent
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jul 2007
    Location
    Grand Rapids, MI
    Posts
    7
    Your work here has actually taught me quite a bit about UDFs and I appreciate that very much, Thank you!

    But what I'm looking for is something closer to what this SQL generates.



    Code:
    USE Northwind
    GO
    
    SELECT OrderID,
    coalesce(MAX(CASE OD.rowno WHEN 1 THEN P.ProductName END), '') AS Product1,
    coalesce(MAX(CASE OD.rowno WHEN 2 THEN P.ProductName END), '') AS Product2,
    coalesce(MAX(CASE OD.rowno WHEN 3 THEN P.ProductName END), '') AS Product3,
    coalesce(MAX(CASE OD.rowno WHEN 4 THEN P.ProductName END), '') AS Product4,
    coalesce(MAX(CASE OD.rowno WHEN 5 THEN P.ProductName END), '') AS Product5,
    coalesce(MAX(CASE OD.rowno WHEN 6 THEN P.ProductName END), '') AS Product6,
    coalesce(MAX(CASE OD.rowno WHEN 7 THEN P.ProductName END), '') AS Product7
    FROM (SELECT a.OrderID, a.ProductID,
    rowno = (SELECT COUNT(*)
    FROM [Order Details] b
    WHERE b.OrderID = a.OrderID
    AND b.ProductID <= a.ProductID)
    FROM [Order Details] a) AS OD
    JOIN Products P ON P.ProductID = OD.ProductID
    GROUP BY OD.OrderID
    ORDER BY OD.OrderID
    Use Northwind database and assume [Order Details] as parent and [Products] as the child. See how all the data between the two tables are displayed in one row (but separate NAMED columns: Product1, Product2, ... etc.)? That's what I'm looking for. If I could write this code into a View (Which I can't) then I could query against the returned dataset like this.

    SELECT * FROM OrderProductView WHERE Product1 = 'Chang'

    and I would get all the same columns, but only including the rows with OrderIDs: {10255, 10258, 10264, etc}.


    The problem with the above code is that I HAVE to know the number of "child" (Product) elements expected per order at design time. Also, the CASE construct is not valid in a View.
    Last edited by brianmhowell; 07-26-07 at 17:34.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    So you don't care about a tree, just a key and all it's attributive rows?

    Maybe something like

    http://weblogs.sqlteam.com/brettk/ar...2/23/4171.aspx
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jul 2007
    Location
    Grand Rapids, MI
    Posts
    7
    Yes! This appears to be exactly what I've been looking for. Thank you, thank you, thank you.
    I was beginning to think this could only be executed in code outside the SQL.
    I need to play with this a little to fully understand it all, but I think this will give me the results I need.
    Thank you Brett for your patience and all your help!

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just cut and paste the code example to see how it works

    Good Luck


    ...oh, and you can buy me a margarita and we'll call it even
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jul 2007
    Location
    Grand Rapids, MI
    Posts
    7
    Next time I'm in the Jersey area I might do just that. I really appreciate it.
    And if you're ever in Grand Rapids...

  13. #13
    Join Date
    Jul 2007
    Location
    Grand Rapids, MI
    Posts
    7
    One more question...
    My query is now too big to store in a local variable... I've managed to write the generated query to a file. Is there anyway I can execute this query from a text file?

Posting Permissions

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