I was hoping someone could help me with the format of the following query?
I have the following table:
Code: Select all
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;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