how to format a query

davidklonski

Hello

I was hoping someone could help me with the format of the following query?
I have the following table:

CREATE TABLE people (
  ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
  First_Name VARCHAR(255) NOT NULL,
  Last_Name VARCHAR(255) NOT NULL,
  # more attributes go here
  Father_ID INT UNSIGNED NOT NULL,
  Mother_ID INT UNSIGNED NOT NULL,
  PRIMARY KEY  (ID)
) ENGINE=MYISAM DEFAULT CHARSET=latin1;

since each person can only have a single father and a single mother, I put their IDs in the same table. The Father_ID attribute behaves like a foreign key to the ID key in the same table (the same goes for the mother).
A missing father is indicated with Father_ID = 0
A missing mother is indicated with Mother_ID = 0

I need to extract the following info:
select all the people who match a certain criteria (for example First_Name is 'John') and for each matching person, also select his father.
For both the people who matched the search and for their fathers I need to get all the attributes (probably using *)

An added bonus would be to return the people sorted with each person followed by his father.

thanks for advance for any help

BuzzLY

You basically need to treat the table as two separate tables -- one as "children" and one as "fathers." Then it works just like any other relational query:

SELECT * 
   FROM people AS children, people AS fathers 
   WHERE children.Father_ID = fathers.ID 
      AND children.First_Name = 'John';

davidklonski

I just realized that I forgot an important requirement:
If a person doesn't have a father, still get him too.
However, if the person HAS a father, extract the father.

BuzzLY

SELECT * 
   FROM people AS children LEFT JOIN people AS fathers 
   ON children.Father_ID = fathers.ID 
      WHERE children.First_Name LIKE 'John%';

davidklonski

Here is an example of data and the results the query should return:

data:

ID   First_Name     Last_Name    Father_ID    Mother_ID
1     'John'        'Thomas'        2            0
2     'Mark'        'Thomas'        0            80
3     'Rachel'      'Steeler'       4            0
4     'Jeff'        'Steeler'       0            5

Now assume that I want to get all the people who'se first name begins with 'j' and for every person found, also return his father (if one exists (father_id <> 0). Make sure that each person is only returned once (a certain person can be ther father of two distinct people, but he should be returned only once)

So the result of the query will be:

ID   First_Name     Last_Name    Father_ID    Mother_ID
1     'John'        'Thomas'        2            0
2     'Mark'        'Thomas'        0            80
4     'Jeff'        'Steeler'       0            5

1 and 4 because they match they condition (first name begins with 'j') and 2 because he is the father of 1 (who matched the condition).

The father should appear right after his son in the results (if this is possible).

can anyone show me how this can be acheived in SQL?
I am using MySQL 5.0

thanks in advance

BuzzLY

If you use this query:

SELECT ch.ID, ch.First_Name, ch.Last_Name, f.ID AS Father_ID, 
       f.First_Name AS Father_First, f.Last_Name AS Father_Last
   FROM people AS ch
   LEFT JOIN people AS f 
   ON ch.Father_ID = f.ID
   WHERE ch.First_Name LIKE 'J%';

You will get this result:

ID   First_Name   Last_Name   Father_ID   Father_First   Father_Last  
--   ----------   ---------   ---------   ------------   -----------
1     John         Thomas         2        Mark           Thomas 
4     Jeff         Steeler       NULL      NULL           NULL

With the information in this format, you should have no trouble extracting it and putting it on your page in whatever order you desire. Because the father info is grouped with the son, it will be easy to keep them together in your page.

If you need help with the PHP side of this, let me know. It shouldn't be too difficult.

davidklonski

in this query, the data is important, but the number of rows must also match the number of people.
The reason is because I am using limit X,Y on that query.
So if I expect there to be Y people returned, the query may return less then Y rows, because each row now contains up to 2 people.

this messes up my browsing mechanism.