fancy Query

[jhoppper]

OK, for various reasons, I want to convert a nice relational database into a single flat table.

Currently I have a tbl_household table (with ID) and a tbl_person table (with ID) which has a parent bool value. I need a single table (ie query) which returns:
Household & person 1 (where parent == true) & Person 2 (where parant = true) & person 3 (where parent == false) & person 4 (where parent == false) & person 5 (where parent == false)

Ie the whole family in one table (a family is assumed to have no more that 2 parents, and no more than 3 children, but can have less)

I have got a version running that give me something like this, but I get 2 rows per houshold, with the parents swapped (I was doing the simpler, without kids case)

Can this be done in SQL (access) and if so how?

 

[rac2]

Presumably the person table has a column for the household ID. You probably get two rows per household because there are two parents per household. You say that is just a partial version? And you are using Access.

The solution would be straightforward if the person table had a column such as person with values {1,2,3,4,5}, or {mom, dad, kid1,kid2, kid3}. I bet you dont have that. I bet you may have any number of rows per household as well. These are database design points that can simplify or complicate queries.

You might want to create and save queries that give you partial results which can then be combined for the final solution.

This query will give one row per household with one parent.

SELECT household_id, MIN(person_id) AS Parent1
FROM Persons
WHERE parent = true
GROUP BY household_id

Save this query and name it PersonOne.

This query will give the other parent.

SELECT p.household_id, p.person_id AS Parent2
FROM Persons p
LEFT JOIN PersonOne p1 ON
       p1.person_id = p.person_id
WHERE p.parent = true
  AND p1.person_id IS NULL

Save this query and name it PersonTwo.

This query will give the first kid, (not necessarily the first born).

SELECT household_id, MIN(person_id) AS Kid1
FROM Persons
WHERE parent = false
GROUP BY household_id

Save this and name it PersonThree.


I leave PersonFour for you.


This query will give the third kid. (Thank goodness for family planning.)

SELECT p.household_id, p.person_id AS Kid3
FROM Persons p
LEFT JOIN PersonThree p3 ON
       p3.person_id = p.person_id
LEFT JOIN PersonFour p4 ON
       p4.person_id = p.person_id
WHERE p.parent = false
  AND p3.person_id IS NULL
  AND p4.person_id IS NULL

Seems plausible.


Now for the final result.

SELECT h.household_id,
       p1.person_id,
       p2.person_id,
       p3.person_id,
       p4.person_id,
       p5.person_id
FROM Households h
LEFT JOIN PersonOne   p1 ON p1.household_id = h.household_id
LEFT JOIN PersonTwo   p2 ON p2.household_id = h.household_id
LEFT JOIN PersonThree p3 ON p3.household_id = h.household_id
LEFT JOIN PersonFour  p4 ON p4.household_id = h.household_id
LEFT JOIN PersonFive  p5 ON p5.household_id = h.household_id

Let me know how it works or whether you have some way to identify the five persons.