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

[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

[r937]

there are still unresolved problems

let's say you search for "j"

you get joe and john

and they both have the same father

where do you want the father sorted?

[davidklonski]

the father should be sorted after the first joe.
but for simplicity, assume that the data allows for a father to have a single son

[r937]

for simplicity? no way, that just makes it worse!!

i'm gonna leave the ORDER BY up to you

select son.ID
     , son.First_Name
     , son.Last_Name
     , son.Father_ID
     , son.Mother_ID
  from people as son
 where son.First_Name = 'John'       
union 
select dad.ID
     , dad.First_Name
     , dad.Last_Name
     , dad.Father_ID
     , dad.Mother_ID
  from people as son
left outer
  join people as dad
    on son.Father_ID = dad.ID
 where son.First_Name = 'John'

[davidklonski]

does that query take into account the possiblity that a person whose first name is 'john' may also have a father whose first name is 'john'? I am afraid of the possibilty that the same person will be selected twice.

What section of the query makes sure this doesn't happen?

[r937]

dude, come on, please have some faith in me

run the darned thing and see for yourself what happens

what? you don't have a test table?

[davidklonski]

I got a problem in the query when I tried to select more data by joining the son and father table with a work table like so:

select son.ID
     , son.First_Name
     , son.Last_Name
     , son.Father_ID
     , son.Mother_ID
     , work.BossName
  from people as son
         , work
 where son.First_Name = 'John' AND
          work.employeeID = son.ID
union 
select dad.ID
     , dad.First_Name
     , dad.Last_Name
     , dad.Father_ID
     , dad.Mother_ID
     , work.BossName
  from people as son
         , work
left outer
  join people as dad
    on son.Father_ID = dad.ID
 where son.First_Name = 'John' AND
          work.employeeID = dad.ID;

I am getting the same incorrect BossName for the sons. The bossname I am getting belongs to one of the sons, but it gets repeated to all of them...
what is wrong?

[r937]

tip: do not mix table list syntax with JOIN syntax

in the first subquery, use an INNER JOIN

in the second query, use another LEFT OUTER after the one that's there

[davidklonski]

thanks for the help.
That solved the problem.

Just for my knowledge, what do you mean by table list syntax and while is it not safe to mix it with JOIN syntax?

thanks again
David

[r937]

it's just too confusing

plus, it can generate incorrect results

consider FROM X LEFT OUTER JOIN Y ON X.a=Y.b

then FROM X,Z LEFT OUTER JOIN Y ON X.a=Y.B WHERE Z.c = Y.b

what happens in rows where Y.b is null? the WHERE condition is false and you get Z rows joined to every X