SimpleSql.sql

drop table if exists parent
create table parent
(
  id int primary key identity(1,1),
  name varchar(50)
)
insert into parent (name) values ('sue'), ('michael'),  ('sandy'), ('doug')

drop table if exists child
create table child
(
  id int primary key identity(1,1),
  petFk int, 
  dadFk int, 
  momFk int, 
  name varchar(50)
)
insert into child (petFk, dadFk, momFk, name) values (4, 2, 1, 'sean'), (null, 4, null, 'jerry')

drop table if exists pet
create table pet
(
  id int primary key identity(1,1),
  name varchar(50)
)
insert into pet (name) values ('apollo'), ('paul'), ('speck'), ('spot');

GO
CREATE OR ALTER FUNCTION getfamily
(
  @childId INTEGER
) 
RETURNS TABLE AS RETURN
SELECT 
  child.name AS Child
	, mom.name AS Mom
	, dad.name AS Dad
	, pet.name AS Pet
	, CONCAT (mom.name, ' & ', dad.name) AS Parents
	, GETDATE() AS UpdatedDate
FROM child
LEFT JOIN parent AS mom
	ON child.momFk = mom.id
LEFT JOIN pet AS pet
	ON child.petFk = pet.id
INNER JOIN parent AS dad
	ON child.dadFk = dad.id
WHERE child.Id = @childId
	OR @childId IS NULL

GO
SELECT * FROM getfamily(1);
SELECT * FROM getfamily(3);
SELECT * FROM getfamily(NULL);