SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to format a query

    Hello

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

  2. #2
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is an example of data and the results the query should return:

    data:
    Code:
    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:
    Code:
    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

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    for simplicity? no way, that just makes it worse!!

    i'm gonna leave the ORDER BY up to you

    Code:
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •