13 messages in com.mysql.lists.mysqlRe: RE: Join-question
FromSent OnAttachments
Chris Williams02 Dec 2002 20:58 
cwil...@rochester.rr.com02 Dec 2002 21:01 
Egor Egorov03 Dec 2002 05:49 
Michal Franc03 Dec 2002 09:18 
cwil...@rochester.rr.com03 Dec 2002 12:02 
Michelle de Beer05 Dec 2002 07:45 
Roger Baklund05 Dec 2002 08:30 
Ryan Fox05 Dec 2002 08:32 
Doug Thompson05 Dec 2002 08:40 
Victoria Reznichenko05 Dec 2002 09:00 
Egor Egorov05 Dec 2002 09:00 
Egor Egorov05 Dec 2002 09:00 
Victoria Reznichenko06 Dec 2002 06:36 
Subject:Re: RE: Join-question
From:Victoria Reznichenko (vict@ensita.net)
Date:12/06/2002 06:36:38 AM
List:com.mysql.lists.mysql

Hello Joseph,

Thursday, December 05, 2002, 8:39:18 PM, you wrote:

NJ> Victoria,

NJ> I am trying to get the hang of this also. In your statement below you NJ> show, what appears to me, two tables, mytest ( I see this one ) and t1. NJ> Where did table t1 come from? Could you explain your sql a little more NJ> if that would not be to much of a problem? Thank you for you patience.

'mytable' - ia a real table. t1, t2, and t3 are aliases. I use a self join - join 'mytable' with the 'mytable' on the conditions t1.rootid=t2.uid to get the name for 'rootid' and then one more self join to get the name for 'parentid'.

NJ> -joseph

NJ> -----Original Message----- NJ> From: Victoria Reznichenko [mailto:vict@ensita.net] NJ> Sent: Thursday, December 05, 2002 9:01 AM NJ> To: mys@lists.mysql.com NJ> Subject: re: Join-question

NJ> Michelle, NJ> Thursday, December 05, 2002, 5:46:03 PM, you wrote:

MdB>> I believe this question is solved by a join, but I MdB>> haven't really got a hang of it.

MdB>> My table: MdB>> -------------------------------------- MdB>> | uid | rootid | parentid | name | MdB>> -------------------------------------- MdB>> | 1 | 0 | 0 | name1 | MdB>> | 2 | 1 | 1 | name2 | MdB>> | 3 | 1 | 2 | name3 | MdB>> | 4 | 1 | 3 | name4 | MdB>> | 5 | 1 | 2 | name5 | MdB>> ...

MdB>> How do I get this (WHERE uid=5): MdB>> ------------------------------------------ MdB>> | rootid_name | parentid_name | name | MdB>> ------------------------------------------ MdB>> | name1 | name2 | name5 | MdB>> ------------------------------------------

MdB>> If you need more info, please tell me.

NJ> Yes, JOIN is what you need. NJ> Something like that: mysql>> SELECT t1.uid, t1.name, t2.name, t3.name ->> FROM mytest t1 ->> LEFT JOIN mytest t2 ON t1.rootid=t2.uid ->> LEFT JOIN mytest t3 ON t1.parentid=t3.uid ->> WHERE t1.uid=5; NJ> +------+-------+-------+-------+ NJ> | uid | name | name | name | NJ> +------+-------+-------+-------+ NJ> | 5 | name5 | name1 | name2 | NJ> +------+-------+-------+-------+ NJ> 1 row in set (0.00 sec)