 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Java Forum / General / February 2007mysql problem
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 > hi all, > [quoted text clipped - 7 lines] > 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 > > hi all, > [quoted text clipped - 11 lines] > > 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 > > > hi all, > [quoted text clipped - 19 lines] > > 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 > 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" <shailajabt...@gmail.com> wrote: > >> i have to get the tree sturcture of that boss( child boss s immediate [quoted text clipped - 14 lines] > > - Lew ThanQ to all > "sh" <shailajabt...@gmail.com> wrote: > >> i have to get the tree sturcture of that boss( child boss s immediate [quoted text clipped - 14 lines] > > - 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 ... > 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 > > I don't think it can be done in one SELECT query, > [quoted text clipped - 4 lines] > > 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 ... > SELECT t1.id AS employee_id, @boss := t2.id AS boss_id > FROM employees AS t1 JOIN employees AS t2 [quoted text clipped - 7 lines] > which is reasonable enough to expect. > Hence the danger of such an assumption. None of this has to do with Java. - Lew Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.