help with multiple table query please

[chiprivers]

I have a database containing information I have collated with reference to my family tree.  The database includes the tables below:

individuals -  individual_ID, surname_birth, first_names_birth, sex  (contains basic details for each person)

births - individual, date_day, date_month, date_year (contains details of birth and connects to individuals using individual_ID = individual)

links - link_from, link_to, link (records connections between people where link_from and link_to are the individual_ID's of the people and link is the relationship between the two individuals)

link_types - link, link_group (this puts the link entries into categories where links.link = link_types.link and then the link_group would be one of either "Parent", "Sibling", "Spouse" or "Child")

What I am struggling to do is extract the name and birth details of any individual that shares a common link_group and is related to a selected individual, and then sort the results in order of birth date.

I am thinking along the lines of:

SELECT surname_birth, first_names_birth, sex, date_day, date_month, date_year, link FROM individuals, births, links, link_types WHERE individuals.individual_ID = births.individual AND individuals.individual_ID = links.link_to AND links.link_from = [slected person] AND links.link = link_types.link AND link_types.link_group = [selected group] ORDER BY births.date_year, births.date_month, births. date_day ASC

Any help would be very much appreciated, many thanks.

[shoz]

The query you posted appears fine as long as for every link_from and link_to relationship you have the reverse as well.

ie
[code]
link_from  | link_to  | link
-----------------------------
1          | 2        | 3
2          | 1        | 3
[/code]

Do you notice any problems with the resullts of the query?

The following syntax may be easier to follow.

[code]
SELECT
surname_birth, first_names_birth, sex,
date_day, date_month, date_year, link
FROM
links AS l
INNER JOIN
individuals AS i
ON
l.link_to = i.individual_ID
INNER JOIN
link_types AS lt
ON
l.link = lt.link
INNER JOIN
births AS b
ON
i.individual_ID = b.individual
WHERE
l.link_from = [selected_person]
AND
lt.link_group = [selected_group]
ORDER BY
b.date_year, b.date_month, b.date_day
[/code]

You should also consider using a single DATE column to store the birth dates.

[chiprivers]

I'll give that query a try and post my results. 

I was going to use one column for the date to hold a date stamp but that would not allow dates prior to 1970.  Obviously, working on a family tree, there are going to be a large number of dates prior to 1970.  DO you have any other suggestions for the date?  I know my solution means having three columns but it seems straight forward enough without any causing any problems.

[shoz]

[quote author=chiprivers link=topic=114513.msg466090#msg466090 date=1163181544]
I was going to use one column for the date to hold a date stamp but that would not allow dates prior to 1970.  Obviously, working on a family tree, there are going to be a large number of dates prior to 1970.  DO you have any other suggestions for the date?
[/quote]

The DATE type in MYSQL is not the same as a unix timestamp. The DATE type has no issues with dates prior to 1970.

[quote=http://dev.mysql.com/doc/refman/4.1/en/date-and-time-type-overview.html]
DATE

A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but allows you to assign values to DATE columns using either strings or numbers.
[/quote]

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-types.html

[chiprivers]

Would there be any advantage of changing my dates to this storage type?

I have already stored a lot of info in my database and so unless there is any notable advantage, I will probably keep as is.

Many thanks on the help with the select statement, it is working perfectly. ;D

[fenway]

Never use TIMESTAMP... ever.  It's a retarded column type that should never exist.  DATE/DATETIME are the only ways to go, and it's  simple update statement to convert it all.

[chiprivers]

I did try and use the date and datetime column types in MySQL on a previous project but I didn't get along with them very well because the default format is not UK standard.  I couldn't find a simple way around that which is why I switched to using timestamp, for that project the restrictions did not pose a problem.

[fenway]

You can use DATE_FORMAT() on the way out of the DB to display your dates however you desire, but this should never affect your choice of column type.