Professional Web Applications Themes

help on SQL statement - MySQL

Hi, I have two tables: the first one has all details about persons, the second associate the id of each person to the id of his team leader (id_boss). Bosses themselves are present in the first table DETAILS_TBL id_person first_name name PERSON_IN_GROUP_TBL id_person id_boss I must make a query to have this result [employee] - [boss] john - smith - jack - doe michael - ross - jack - doe jack - doe - jack - doe tom - cruise - al - pacino etc I use mysql and postgresql but I can't imagine a sql statement to do this. ...

  1. #1

    Default help on SQL statement

    Hi,

    I have two tables: the first one has all details about persons, the
    second associate the id of each person to the id of his team leader
    (id_boss). Bosses themselves are present in the first table

    DETAILS_TBL
    id_person
    first_name
    name

    PERSON_IN_GROUP_TBL
    id_person
    id_boss

    I must make a query to have this result

    [employee] - [boss]
    john - smith - jack - doe
    michael - ross - jack - doe
    jack - doe - jack - doe
    tom - cruise - al - pacino

    etc

    I use mysql and postgresql but I can't imagine a sql statement to do
    this.

    Anyone can help me?
    :-)

    BR,

    Filippo

    filippo2991@virgilio.it Guest

  2. #2

    Default Re: help on SQL statement

    [email]filippo2991virgilio.it[/email] wrote:
    > Hi,
    >
    > I have two tables: the first one has all details about persons, the
    > second associate the id of each person to the id of his team leader
    > (id_boss). Bosses themselves are present in the first table
    >
    > DETAILS_TBL
    > id_person
    > first_name
    > name
    >
    > PERSON_IN_GROUP_TBL
    > id_person
    > id_boss
    >
    > I must make a query to have this result
    >
    > [employee] - [boss]
    > john - smith - jack - doe
    > michael - ross - jack - doe
    > jack - doe - jack - doe
    > tom - cruise - al - pacino
    >
    > etc
    >
    > I use mysql and postgresql but I can't imagine a sql statement to do
    > this.
    >
    > Anyone can help me?
    > :-)
    >
    > BR,
    >
    > Filippo
    SELECT a.first_name, a.name, b.first_name, b.name
    FROM details_tbl a
    JOIN (
    person_in_group_tbl c, details_tbl b
    ) ON ( a.id_person = c.id_person
    AND c.id_boss = b.id_person )


    Paul Lautman Guest

  3. #3

    Default Re: help on SQL statement

    Paul Lautman wrote:
    > [email]filippo2991virgilio.it[/email] wrote:
    >> Hi,
    >>
    >> I have two tables: the first one has all details about persons, the
    >> second associate the id of each person to the id of his team leader
    >> (id_boss). Bosses themselves are present in the first table
    >>
    >> DETAILS_TBL
    >> id_person
    >> first_name
    >> name
    >>
    >> PERSON_IN_GROUP_TBL
    >> id_person
    >> id_boss
    >>
    >> I must make a query to have this result
    >>
    >> [employee] - [boss]
    >> john - smith - jack - doe
    >> michael - ross - jack - doe
    >> jack - doe - jack - doe
    >> tom - cruise - al - pacino
    >>
    >> etc
    >>
    >> I use mysql and postgresql but I can't imagine a sql statement to do
    >> this.
    >>
    >> Anyone can help me?
    >> :-)
    >>
    >> BR,
    >>
    >> Filippo
    >
    > SELECT a.first_name, a.name, b.first_name, b.name
    > FROM details_tbl a
    > JOIN (
    > person_in_group_tbl c, details_tbl b
    > ) ON ( a.id_person = c.id_person
    > AND c.id_boss = b.id_person )
    Also, if not all employess have entries in the PERSON_IN_GROUP_TBL, but you
    wish to have them in the final output, then do:

    SELECT a.first_name, a.name, b.first_name, b.name
    FROM details_tbl a
    LEFT JOIN (
    person_in_group_tbl c
    ) ON ( a.id_person = c.id_person )
    LEFT JOIN (
    details_tbl b
    ) ON ( c.id_boss = b.id_person )


    Paul Lautman Guest

Similar Threads

  1. SQL Statement Help
    By cwwilly in forum ASP Database
    Replies: 9
    Last Post: April 15th, 04:55 PM
  2. [PHP] SQL statement
    By Chris W. Parker in forum PHP Development
    Replies: 14
    Last Post: September 28th, 03:48 PM
  3. if statement with url
    By John in forum PHP Development
    Replies: 5
    Last Post: September 11th, 03:13 AM
  4. if statement
    By alexz in forum ASP
    Replies: 8
    Last Post: July 15th, 04:57 PM
  5. Can i say OR in an if/then statement
    By crispy in forum ASP
    Replies: 6
    Last Post: July 9th, 07:27 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139