0

I have three tables in MsSQL to store data.

First one is the Children contains the following rows ID, Name, Age, FatherID, SchoolID

The second one contains the data of their fathers, like this FatherID, Name, Age

The third one is the School with SchoolID and SchoolName, Address, etc.

(The relation is: multiple children can have the same father, obviously :) )

What I want to list is all the family members related to a given SchoolID.

I know a cursor based solution:

*Cursor is working with two temporary tables, at first step selecting the children with a given schoolid into one temporary table(@tempfamilymembers).

Second step is to select the distinct fatherids from this table and save it to the second temporary table(@fatherids).

Then, I would loop over these fatherids to insert fathers with matching fatherids(from @fatherids) into the first temporary table(@tempfamilymembers).

Then, I can select all from @tempfamilymembers to complete the query*

The cursor based solution is too difficult, and relatively slow plus I heard while loops are showing better performance and a set-based approach would be even better.

My question is : Can I achieve this somehow, without creating a cursor? (I want to list the fathers present in this list only one time, even if he has more than one children.)

Newbie1001
  • 131
  • 11
  • Do you know how to JOIN tables in a query? – Tab Alleman Feb 18 '19 at 16:30
  • 1
    Not sure if this helps you.. https://blogs.msdn.microsoft.com/simonince/2007/10/17/hierarchies-with-common-table-expressions/ CTE's using UNION ALL is normal way to solve this problem. I'm more familiar with Oracle version of this - but you can do things like depth first or breadth first sorting. I assume SQL Server has something similar. It's easy to add a depth from root calculated field if you Google a bit more too. That way it makes it easy to look at things from siblings perspective too – JGFMK Feb 18 '19 at 16:37
  • Sure. Not only is it possible not to use a cursor but it is recommended. – Sean Lange Feb 18 '19 at 16:45
  • @TabAlleman I am a little bit confused about working with joins. I know the basics, but It seems a more advanced problem. Could you explain me how a join can be used in this situation? – Newbie1001 Feb 18 '19 at 17:58
  • @JGFMK I will check it. – Newbie1001 Feb 18 '19 at 17:59
  • 1
    https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL - this is quite handy too - it's broader in scope - as in cross vendor implementations - but there's some good stuff there too. – JGFMK Feb 18 '19 at 18:04
  • @JGFMK I think you may have mis-read the question. This isn't a recursive scenario. The parents and children are in separate tables in this case. A single, simple parent-child join should be all that's needed here. – Tab Alleman Feb 18 '19 at 18:09
  • I think you meant columns not rows too – JGFMK Feb 18 '19 at 18:11
  • Also I guess a father can have children in multiple schools – JGFMK Feb 18 '19 at 18:21
  • 1
    check out the answer I have given. Be sure to accept if you find it works for you - let me know if need any further assistance on this one. – JGFMK Feb 18 '19 at 18:52

1 Answers1

1
WITH family_members (id, name, age, role) AS
(
  SELECT FatherID, Name, Age, 'father'
  FROM Fathers 
  WHERE FatherID IN(SELECT DISTINCT FatherID
                    FROM Children 
                    WHERE SchoolID = 1) -- 1) Put School to search here
  UNION ALL
  SELECT ID, Name, Age, 'child '
  FROM Children
  WHERE SchoolID = 1 -- 2) Put School to search here
)
SELECT * FROM family_members

  • If you were to put some data in rextester you could test this out. Let me know what you think.

  • If columns are of differing types (Children vs Father) you may have to CAST columns.

  • As I mentioned originally in comments CTE's/UNION ALL are way to go. ;-)

JGFMK
  • 8,425
  • 4
  • 58
  • 92