«

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 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

On Feb 8, 5:51 pm, Alex Hunsley <red...@bluebottle.com> 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

On Feb 8, 5:25 am, "sh" <shailajabt...@gmail.com> wrote:
> On Feb 8, 5:51 pm, Alex Hunsley <red...@bluebottle.com> 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

"sh" <shailajabt...@gmail.com> 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

On Feb 9, 5:29 am, Lew <l...@nospam.lewscanon.com> wrote:
> "sh" <shailajabt...@gmail.com> 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

On Feb 9, 1:29 am, Lew <l...@nospam.lewscanon.com> wrote:
> "sh" <shailajabt...@gmail.com> 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

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

On Feb 9, 9:36 pm, Lew <l...@nospam.lewscanon.com> 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 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