mysql problem

Discussion in 'Java' started by sh, Feb 8, 2007.

  1. sh

    sh Guest

    hi all,

    problem with the sql query.



    The Employees table has a primary key for the employee Id and the Boss
    column has a foreign key that references the employee Id of the
    Employees boss. There is a convention that the Id and Boss columns of
    the big boss are the same.

    In order to see the basics of a hierarchical pattern , how to write
    query for mysql DB.

    thanks in advance
    sh, Feb 8, 2007
    #1
    1. Advertising

  2. sh

    Alex Hunsley Guest

    sh wrote:
    > hi all,
    >
    > problem with the sql query.
    >
    >
    >
    > The Employees table has a primary key for the employee Id and the Boss
    > column has a foreign key that references the employee Id of the
    > Employees boss. There is a convention that the Id and Boss columns of
    > the big boss are the same.
    >
    > In order to see the basics of a hierarchical pattern , how to write
    > query for mysql DB.


    Your question isn't clear - what do you want the query to show?

    Also, your question could use having a question mark at end:
    http://en.wikipedia.org/wiki/Question_mark
    Alex Hunsley, Feb 8, 2007
    #2
    1. Advertising

  3. sh

    sh Guest

    On Feb 8, 5:51 pm, Alex Hunsley <> wrote:
    > sh wrote:
    > > hi all,

    >
    > > problem with the sql query.

    >
    > > The Employees table has a primary key for the employee Id and the Boss
    > > column has a foreign key that references the employee Id of the
    > > Employees boss. There is a convention that the Id and Boss columns of
    > > the big boss are the same.

    >
    > > In order to see the basics of a hierarchical pattern , how to write
    > > query for mysql DB.

    >
    > Your question isn't clear - what do you want the query to show?
    >
    > Also, your question could use having a question mark at end:http://en.wikipedia.org/wiki/Question_mark




    sorry,

    i have to get the tree sturcture of that boss( child boss s immediate
    parentBoss and parentBoss's immediate parentBoss and soon........)
    inorder to get this result how to a write a query in mysql db

    thankQ
    sh, Feb 8, 2007
    #3
  4. On Feb 8, 5:25 am, "sh" <> wrote:
    > On Feb 8, 5:51 pm, Alex Hunsley <> wrote:
    >
    >
    >
    > > sh wrote:
    > > > hi all,

    >
    > > > problem with the sql query.

    >
    > > > The Employees table has a primary key for the employee Id and the Boss
    > > > column has a foreign key that references the employee Id of the
    > > > Employees boss. There is a convention that the Id and Boss columns of
    > > > the big boss are the same.

    >
    > > > In order to see the basics of a hierarchical pattern , how to write
    > > > query for mysql DB.

    >
    > > Your question isn't clear - what do you want the query to show?

    >
    > > Also, your question could use having a question mark at end:http://en.wikipedia.org/wiki/Question_mark

    >
    > sorry,
    >
    > i have to get the tree sturcture of that boss( child boss s immediate
    > parentBoss and parentBoss's immediate parentBoss and soon........)
    > inorder to get this result how to a write a query in mysql db
    >
    > thankQ


    Per my understanding, you are looking for self-joins. Google for
    'mysql self join' and you should be able to get some information along
    these lines. I believe this is a pretty common problem statement
    intended to be solved using self joins.

    -cheers,
    Manish
    Manish Pandit, Feb 8, 2007
    #4
  5. sh

    Lew Guest

    "sh" <> wrote:
    >> i have to get the tree sturcture of that boss( child boss s immediate
    >> parentBoss and parentBoss's immediate parentBoss and soon........)
    >> inorder to get this result how to a write a query in mysql db


    Manish Pandit wrote:
    > Per my understanding, you are looking for self-joins. Google for
    > 'mysql self join' and you should be able to get some information along
    > these lines. I believe this is a pretty common problem statement
    > intended to be solved using self joins.


    That's right. You want something along the lines of

    .... FROM T T1 JOIN T T2 ON T1.boss = T2.person ...

    Perhaps the people in one of the database or MySQL newsgroups can help better
    than here in the Java world. They could further help with information on how
    to structure foreign and primary keys to support this, and what dangers may lurk.

    - Lew
    Lew, Feb 9, 2007
    #5
  6. sh

    sh Guest

    On Feb 9, 5:29 am, Lew <> wrote:
    > "sh" <> wrote:
    > >> i have to get the tree sturcture of that boss( child boss s immediate
    > >> parentBoss and parentBoss's immediate parentBoss and soon........)
    > >> inorder to get this result how to a write a query in mysql db

    > Manish Pandit wrote:
    > > Per my understanding, you are looking for self-joins. Google for
    > > 'mysql self join' and you should be able to get some information along
    > > these lines. I believe this is a pretty common problem statement
    > > intended to be solved using self joins.

    >
    > That's right. You want something along the lines of
    >
    > ... FROM T T1 JOIN T T2 ON T1.boss = T2.person ...
    >
    > Perhaps the people in one of the database or MySQL newsgroups can help better
    > than here in the Java world. They could further help with information on how
    > to structure foreign and primary keys to support this, and what dangers may lurk.
    >
    > - Lew


    ThanQ to all
    sh, Feb 9, 2007
    #6
  7. On Feb 9, 1:29 am, Lew <> wrote:
    > "sh" <> wrote:
    > >> i have to get the tree sturcture of that boss( child boss s immediate
    > >> parentBoss and parentBoss's immediate parentBoss and soon........)
    > >> inorder to get this result how to a write a query in mysql db

    > Manish Pandit wrote:
    > > Per my understanding, you are looking for self-joins. Google for
    > > 'mysql self join' and you should be able to get some information along
    > > these lines. I believe this is a pretty common problem statement
    > > intended to be solved using self joins.

    >
    > That's right. You want something along the lines of
    >
    > ... FROM T T1 JOIN T T2 ON T1.boss = T2.person ...
    >
    > Perhaps the people in one of the database or MySQL newsgroups can help better
    > than here in the Java world. They could further help with information on how
    > to structure foreign and primary keys to support this, and what dangers may lurk.
    >
    > - Lew


    I don't think it can be done in one SELECT query, unless you assume
    the table to be sorted in such an order that bosses are come after
    employees,
    which is dangerous.

    The following query gives you the boss of a specific employee

    SET @employee := 'an_employee_id';

    SELECT t1.id AS employee_id, @boss := t2.id AS boss_id
    FROM employees AS t1 JOIN employees AS t2
    ON t1.boss = t2.id
    WHERE t1.id = @employee;

    Now, if @employee <> @boss, you need to repeat the SELECT query
    after you set

    SET @employee := @boss;

    else you're done.

    This can be achieved by a script procedure in MySQL or it could be
    left to API.

    Otherwise, you can subscribe to the proper mailing list for ask such
    questions at

    http://lists.mysql.com/

    Regards,
    Faton Berisha
    Faton Berisha, Feb 9, 2007
    #7
  8. sh

    Lew Guest

    Lew wrote:
    >> That's right. You want something along the lines of
    >>
    >> ... FROM T T1 JOIN T T2 ON T1.boss = T2.person ...


    Faton Berisha wrote:
    > I don't think it can be done in one SELECT query,


    But the query you yourself show does it in one SELECT statement!

    > SELECT t1.id AS employee_id, @boss := t2.id AS boss_id
    > FROM employees AS t1 JOIN employees AS t2
    > ON t1.boss = t2.id
    > WHERE t1.id = @employee;


    In JDBC terms, rather than PL/SQL or whatever,

    SELECT t1.id AS employee_id, t2.id AS boss_id
    FROM employees AS t1 JOIN employees AS t2
    ON t1.boss = t2.id
    WHERE t1.id = ?

    > unless you assume the table to be sorted in such an order
    > that bosses are come after employees, which is dangerous.


    SQL tables are never in any particular order. No bugbear, no danger.

    Incidentally, the use of artificial, autoincremented "id" keys in database
    implementation is controversial.

    - Lew
    Lew, Feb 9, 2007
    #8
  9. On Feb 9, 9:36 pm, Lew <> wrote:
    > Faton Berisha wrote:
    > > I don't think it can be done in one SELECT query,

    >
    > But the query you yourself show does it in one SELECT statement!
    >
    > > SELECT t1.id AS employee_id, @boss := t2.id AS boss_id
    > > FROM employees AS t1 JOIN employees AS t2
    > > ON t1.boss = t2.id
    > > WHERE t1.id = @employee;

    >


    Not really. The way I understand it is that
    the OP wants the entire hierarchical pattern,
    while the query returns only the boss for a given employee.

    > In JDBC terms, rather than PL/SQL or whatever,
    > [snip]


    It is MySQL.

    > > unless you assume the table to be sorted in such an order
    > > that bosses come after employees, which is dangerous.

    >
    > SQL tables are never in any particular order. No bugbear, no danger.


    My point, exactly. Thus, you don't assume any particular order.
    But, then, the query cannot select all the bosses
    (i.e. the entire pattern) in a single run.

    > Incidentally, the use of artificial, autoincremented "id" keys in database
    > implementation is controversial.


    If such an artificial order was implemented,
    i.e. each boss' record coming after the corresponding employee one,
    then the entire pattern could be selected
    by a slightly modified version of the query,
    something like

    SELECT t1.id AS employee_id, @boss := t2.id AS boss_id
    FROM employees AS t1 JOIN employees AS t2
    ON t1.boss = t2.id
    WHERE t1.id = @employee
    OR (t1.id = @boss and t2.id <> t1.id);

    But even in such a ("controversial") case,
    the order wouldn't hold if, e.g.,
    a boss and an employee switch positions,
    which is reasonable enough to expect.
    Hence the danger of such an assumption.

    >
    > - Lew


    Faton Berisha
    Faton Berisha, Feb 10, 2007
    #9
  10. sh

    Lew Guest

    Faton Berisha wrote:
    ....
    > SELECT t1.id AS employee_id, @boss := t2.id AS boss_id
    > FROM employees AS t1 JOIN employees AS t2
    > ON t1.boss = t2.id
    > WHERE t1.id = @employee
    > OR (t1.id = @boss and t2.id <> t1.id);
    >
    > But even in such a ("controversial") case,
    > the order wouldn't hold if, e.g.,
    > a boss and an employee switch positions,
    > which is reasonable enough to expect.
    > Hence the danger of such an assumption.


    None of this has to do with Java.

    - Lew
    Lew, Feb 10, 2007
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. JL
    Replies:
    0
    Views:
    1,142
  2. Ravi
    Replies:
    6
    Views:
    1,405
    Suchandra Thapa
    Jul 21, 2003
  3. Replies:
    2
    Views:
    6,187
  4. washakie
    Replies:
    4
    Views:
    921
    washakie
    Jan 15, 2008
  5. Jeffrey H. Coffield
    Replies:
    1
    Views:
    1,847
Loading...

Share This Page