I'm working on a database that ties together many tables of data that
represent a family. So I have tables for the 'family' (addresses,
etc) adults (name, bdate), children (name, bdate) etc, between the
adults and children there are added 'family relation' tables (a way to
have /parentskids part of two families for shared custody, etc.)

Now if I want to search for a person's name in the extended family set
- I've tried

SELECT
*
FROM
family AS f,
familyadult AS fa,
familychild AS fc,
child AS c
WHERE
f.family_id = fa.family_id AND
fa.adult_id = a.adult_id AND
f.family_id = fc.family_id AND
fc.child_id = c.child_id
AND
CONCAT(
a.adult_firstname,
a.adult_lastname,
c.child_firstname,
c.child_lastname
)
LIKE '%elmo%';

But that only works if there matches all across the board, (actually
it's worse, there are timestamps involved, but I think I can handle
those)

How would I write the joins for all those connections with left joins
so I can find families without kids, etc.

Larry

On Thu, 16 Aug 2007 23:38:36 +0200, <larry@portcommodore.com> wrote:

> I'm working on a database that ties together many tables of data that
> represent a family. So I have tables for the 'family' (addresses,
> etc) adults (name, bdate), children (name, bdate) etc, between the
> adults and children there are added 'family relation' tables (a way to
> have /parentskids part of two families for shared custody, etc.)

Hmmmz, depending on the real data, I'd use this scheme:

PERSONS
id, name, bdate, etc...

RELATIONS
adult_id (points to persons.id),
child_id (points to persons.id)

So you can simply search in persons. Multiple childs, multiple adults all
possible. If a person can only be a child or an adult (you'd think they
grow...), possible add a flag to PERSONS.

Or possibly, if the practical use is not what I think:

PERSONS
id, name, bdate, etc...

FAMILIES
id,....

RELATIONS
family_id, persons.id, adult_or_child_flag

Single parents, multiple parents, shared custody in different families
with different single or multiple adults, different siblings, ending or
starting a family-tie on certain dates, all possible. And just one table
to search for a person.
--
Rik Wasmus

On Aug 16, 5:41 pm, Rik <luiheidsgoe...@hotmail.com> wrote:

>
> Or possibly, if the practical use is not what I think:
>
> PERSONS
> id, name, bdate, etc...
>
> FAMILIES
> id,....
>
> RELATIONS
> family_id, persons.id, adult_or_child_flag
>
This one is closer, there is significant "family" data (addresses,
etc.)

Very interesting concept, there are different fields for the adults
than children (phones, income sources, etc) and usually more children
than adults . I'll give it some thought though. Still would like a
suggestion on setting up the joins in the structure I was using.

On 17 Aug, 03:22, la...@portcommodore.com wrote:
> On Aug 16, 5:41 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
>
>
>
> > Or possibly, if the practical use is not what I think:
>
> > PERSONS
> > id, name, bdate, etc...
>
> > FAMILIES
> > id,....
>
> > RELATIONS
> > family_id, persons.id, adult_or_child_flag
>
> This one is closer, there is significant "family" data (addresses,
> etc.)
>
> Very interesting concept, there are different fields for the adults
> than children (phones, income sources, etc) and usually more children
> than adults . I'll give it some thought though. Still would like a
> suggestion on setting up the joins in the structure I was using.

Use explicit JOIN syntax rather than comma joins. Then you can replace
the JOIN by LEFT JOIN when there may not be a matching record in the
joinrd to table.

On Aug 17, 1:42 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:

>
> Use explicit JOIN syntax rather than comma joins. Then you can replace
> the JOIN by LEFT JOIN when there may not be a matching record in the
> joinrd to table.

I'm getting closer, but I know something is wrong with this, as I put
just the just the selection in my test on PHPMyAdmin and I get NULL
data for joined tables. I think it is because not everything is
directly joining to the family table.

Any suggestions on doing this?

SELECT
*
FROM
ccfamily AS f
LEFT JOIN ccfamilyadult AS fa ON fa.ccfamily_id =
f.ccfamily_id
LEFT JOIN ccadult AS a ON a.ccadult_id = fa.ccadult_id
LEFT JOIN ccfamilychild AS fc ON fc.ccfamily_id =
f.ccfamily_id
LEFT JOIN ccchild AS c ON c.ccchild_id = fc.ccchild_id
WHERE
CONCAT( a.ccadult_lastname,
a.ccadult_firstname,
c.ccchild_firstname,
c.ccchild_lastname )
LIKE '%$searchstr%'

If this form of SELECT is impossible, could someone tell me.

I read an example with it using an INNER JOIN at the second stage
(familyadult->adult) when I do that I get no records at all (did not
have a WHILE condition so I should have had something return).
Another one was using parentheses to prioritize the joins but it was a
smaller selection of tables and I got lost on the size of this (and
this is a smaller selection of what I have envisioned as well)

Larry

larry@portcommodore.com wrote:
> If this form of SELECT is impossible, could someone tell me.
>
> I read an example with it using an INNER JOIN at the second stage
> (familyadult->adult) when I do that I get no records at all (did not
> have a WHILE condition so I should have had something return).
> Another one was using parentheses to prioritize the joins but it was a
> smaller selection of tables and I got lost on the size of this (and
> this is a smaller selection of what I have envisioned as well)
>
> Larry

Please post sample schema (CREATE TABLE exports from phpmyadmin),
sample data (INSERT INTO exports from pypmyadmin
and a sample required results table.

As t is you have't really told us enough to help you fully.

On Aug 21, 12:47 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
>
> As t is you have't really told us enough to help you fully.

All I was interested was was this possible, and most people do I had a
breakthrough a couple hours after posting. Part of it was a bad bit
of sample data (it always seems to be something like that). But the
other significant part was any of the conditions in the CONCAT are
NULL, CONCAT returns a NULL value. So the updated query would be:

SELECT
*
FROM
ccfamily f
LEFT JOIN ccfamilyadult fa ON f.ccfamily_id =
fa.ccfamily_id
LEFT JOIN ccadult a ON fa.ccadult_id = a.ccadult_id
LEFT JOIN ccfamilychild fc ON f.ccfamily_id =
fc.ccfamily_id
LEFT JOIN ccchild c ON fc.ccchild_id = c.ccchild_id
LEFT JOIN ccservice s ON fc.ccfamilychild_ID =
s.ccfamilychild_id
WHERE
CONCAT(
IF( a.ccadult_lastname IS NULL, '.',
CONCAT( a.ccadult_lastname, a.ccadult_firstname )
),
IF( c.ccchild_firstname IS NULL, '.',
CONCAT( c.ccchild_firstname, c.ccchild_lastname )
)
)
LIKE '%$searchstr%'

This takes care of records missing an adult or child. I think when I
get this finished I'll have a real long-ass query (next is the
datestamp comparisons for matching rows and chaining on more tables).

Anyone made queries that seem to go on forever? Ever hit an effective
limit with MySQL?

On 22 Aug, 04:52, la...@portcommodore.com wrote:
> On Aug 21, 12:47 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>
>
>
> > As t is you have't really told us enough to help you fully.
>
> All I was interested was was this possible, and most people do I had a
> breakthrough a couple hours after posting. Part of it was a bad bit
> of sample data (it always seems to be something like that). But the
> other significant part was any of the conditions in the CONCAT are
> NULL, CONCAT returns a NULL value. So the updated query would be:
>
> SELECT
> *
> FROM
> ccfamily f
> LEFT JOIN ccfamilyadult fa ON f.ccfamily_id =
> fa.ccfamily_id
> LEFT JOIN ccadult a ON fa.ccadult_id = a.ccadult_id
> LEFT JOIN ccfamilychild fc ON f.ccfamily_id =
> fc.ccfamily_id
> LEFT JOIN ccchild c ON fc.ccchild_id = c.ccchild_id
> LEFT JOIN ccservice s ON fc.ccfamilychild_ID =
> s.ccfamilychild_id
> WHERE
> CONCAT(
> IF( a.ccadult_lastname IS NULL, '.',
> CONCAT( a.ccadult_lastname, a.ccadult_firstname )
> ),
> IF( c.ccchild_firstname IS NULL, '.',
> CONCAT( c.ccchild_firstname, c.ccchild_lastname )
> )
> )
> LIKE '%$searchstr%'
>
> This takes care of records missing an adult or child. I think when I
> get this finished I'll have a real long-ass query (next is the
> datestamp comparisons for matching rows and chaining on more tables).
>
> Anyone made queries that seem to go on forever? Ever hit an effective
> limit with MySQL?

Might I suggest
CONCAT(
IFNULL( a.ccadult_lastname, '.',
CONCAT( a.ccadult_lastname, a.ccadult_firstname )
),
IFNULL( c.ccchild_firstname, '.',
CONCAT( c.ccchild_firstname, c.ccchild_lastname )
)
)

On Aug 22, 2:13 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> Might I suggest
> CONCAT(
> IFNULL( a.ccadult_lastname, '.',
> CONCAT( a.ccadult_lastname, a.ccadult_firstname )
> ),
> IFNULL( c.ccchild_firstname, '.',
> CONCAT( c.ccchild_firstname, c.ccchild_lastname )
> )
> )

Wow that's slick, but it should be:

CONCAT(
IFNULL( CONCAT( a.ccadult_lastname,
a.ccadult_firstname ), '.'),
IFNULL( CONCAT( c.ccchild_firstname,
c.ccchild_lastname ), '.')
)

(if the first expression is NULL return the second, otherwise return
the first one)

That makes it so much cleaner, thanks!