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 :-)

Laxmidhar Sahoo · Answer

I could not understand .I rthink you want to use left join for family table

Hakan Axheim · Answer

I have tried with this query and with this query I get the whole family before the divorce. But how to do an SQL where I can search for a specific child or parent and get the whole family. SELECT Family.* FROM Family AS Family WHERE EXISTS ( SELECT TOP 1 1 FROM Family F INNER JOIN Placement AS P ON (P.SSN = F.SSN) WHERE F.DateTom != NULL AND P.DateTom = NULL UNION SELECT 1 FROM Family F INNER JOIN Placement AS P ON (P.SSN = F.SSN) WHERE P.DateTom = NULL AND F.DateTom = NULL) AND Family.DateTom != NULL

Hakan Axheim · Answer

I understand that the SQL statement should be: SELECT F.SSN FROM Families F INNER JON Placement P ON P.SSN = F.SSN WHERE F.DateTom >= GETDATE() But this is not the whole question regarding the requirements that I have listed. I have problems when I search for a child that has no placement. How to search for a sibling in the other family and check if the sibling has a placement. The child that has no placement has a sibling that has placement which fulfills the requirement and shall show the both divided families, i.e the family before divorce and split.

Laxmidhar Sahoo · Answer

SELECT F.SSN FROM Families F INNER JON Placement P ON P.SSN = F.SSN WHERE F.DateTom >= GETDATE()