mysql problem

[sh]

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

 

[Alex Hunsley]

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

 

[sh]

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

 

[Manish Pandit]

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

 

[Lew]

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

 

[sh]

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

 

[Faton Berisha]

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]

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

 

[Faton Berisha]

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

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.

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

 

[Lew]

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