Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > mysql problem

Reply
Thread Tools

mysql problem

 
 
sh
Guest
Posts: n/a
 
      02-08-2007
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

 
Reply With Quote
 
 
 
 
Alex Hunsley
Guest
Posts: n/a
 
      02-08-2007
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
 
Reply With Quote
 
 
 
 
sh
Guest
Posts: n/a
 
      02-08-2007
On Feb 8, 5:51 pm, Alex Hunsley <(E-Mail Removed)> 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

 
Reply With Quote
 
Manish Pandit
Guest
Posts: n/a
 
      02-08-2007
On Feb 8, 5:25 am, "sh" <(E-Mail Removed)> wrote:
> On Feb 8, 5:51 pm, Alex Hunsley <(E-Mail Removed)> 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

 
Reply With Quote
 
Lew
Guest
Posts: n/a
 
      02-09-2007
"sh" <(E-Mail Removed)> 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
 
Reply With Quote
 
sh
Guest
Posts: n/a
 
      02-09-2007
On Feb 9, 5:29 am, Lew <(E-Mail Removed)> wrote:
> "sh" <(E-Mail Removed)> 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

 
Reply With Quote
 
Faton Berisha
Guest
Posts: n/a
 
      02-09-2007
On Feb 9, 1:29 am, Lew <(E-Mail Removed)> wrote:
> "sh" <(E-Mail Removed)> 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

 
Reply With Quote
 
Lew
Guest
Posts: n/a
 
      02-09-2007
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
 
Reply With Quote
 
Faton Berisha
Guest
Posts: n/a
 
      02-10-2007
On Feb 9, 9:36 pm, Lew <(E-Mail Removed)> 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

 
Reply With Quote
 
Lew
Guest
Posts: n/a
 
      02-10-2007
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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: How to insert PDF file in to MySql and read it from MySql toJAVA App Jeffrey H. Coffield Java 1 07-24-2009 12:29 AM
MySQL-python-1.2.2 install with no mysql washakie Python 4 01-15-2008 08:15 PM
"mysql.h: No such file or directory" when building MySQL-python francescomoi@europe.com Python 2 05-11-2005 03:12 PM
DBD:mysql doesn't read mysql option file /etc/my.cnf file JL Perl 0 01-28-2005 03:19 AM
"Pure Python" MySQL module like Net::MySQL Ravi Python 6 07-21-2003 06:53 PM



Advertisments