Need help with an SQL in SqlServer

Question

Hi,

I have a question regarding an SQL query I must do. The requirements for this question are;

The question must be able to search for persons in two families. The family that has a DateTom which are before or equal to today’s date does not exist. This family has been divided into two families because of a divorce previously.

It must be able to search for all persons in the two valid families and present them as one family.

If I search for a child SSN then I must check in the placement table i the child exists there. If this child does not exist in that table I have to check if there is a sibling in the family or a sibling in the divided family that has a placement and if a sibling has a placement the two divided family shall be shown.

The SQL must be in this format

 

SELECT SSN

FROM    Families

INNER JON Placement ON (placement.SSN = Families.SSN)

 

WHERE SSN.Person = &searchSSN

 

i.e that the search statement will be added to the end of the statement.

I have two tables to work with!

One table that has information about the families. They have separate family numbers in that table and a property that describes if it is a child and a parent. If a DateTom has a date before today’s date or a today’s date it is a family that does not exist. If DateTom is NULL then the family exists.

See Family table below;

Familj Id	SSN	Property	DateFrom	DateTom
10	0001	C	20030706	20190201
10	0002	C	20030706	20190201
10	0003	C	20030706	20190201
10	0004	M	20030706	20190201
10	0005	F	20030706	20190201
20	0001	C	20190201
20	0002	C	20190201
20	0003	M	20190201
30	0004	C	20190201
30	0005	F	20190201

 

Childs can exist in another table that holds information about school placement. If I search for the child’s parent or the child and the child does not exist in the placement table the question shall see if a sibling in the same family or a sibling in the other divided family has a placement and if any child in the two divided families has placement I want the question to make a result that consists of the family before it was divided into two families.

The placement table;

SSN	Placement	DateFrom	DateTom
0001	10	20180201	20180901
0001	10	20181020	20190201
0002	10	20180201

As you can see in the placement table child 0003 has no placement. But the query shall be successful because that child has a sibling in his family that has placement and/or has a sibling in the other family that has been divided from the originally family.

It must be able to search for any person in the divided families and dhow them as the origin family if any child has a placement. If no child has placements no family shall be shown.

I hope someone can help me wit this :-)