Re: RE: Join-question
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:[EMAIL PROTECTED]] NJ Sent: Thursday, December 05, 2002 9:01 AM NJ To: [EMAIL PROTECTED] 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) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: JOIN-Question
Hi Mikhail! The query should be released automaticly and it shoulb be something like: SELECT a.*,b.*,c.* FROM ?? WHERE b.field3=5 OR c.field6=16 I want to have all entries wich fit to the condition. But at this time I don't know weather there is a entry with name=test or test2 or not. So when there is none it should return some NULLs otherwise the entries. I already tries using LEFT JOINs, but it doesn't work the way I want it to. Do you think there is a proper way to solve this problem? CU, Peter Mikhail Entaltsev [EMAIL PROTECTED] schrieb am 19.09.02 11:36:16: Peter, If you would like to get such resultset namefield1 field2 field3field4 field5 field6 - test 12 5 6 9 10 test 12 5 6 1112 test2 34 NULLNULL 1516 then you need to use left join for t2, but based only on these information I couldn't help you to implement this query (I don't know how to group by rows). Please, give more information about why do you need exactly this resultset. Best regards, Mikhail. - Original Message - From: Peter Stöcker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 11:03 AM Subject: JOIN-Question Hi there! I have a question on JOINs. First the system: table1: name: t1 entries: name field1 field2 test 1 2 test2 3 4 table2: name t2 entries: name field3 field4 test 5 6 test 7 8 table3: name t3 entries: name field5 field6 test 9 10 test 11 12 test2 13 14 test2 15 16 And here the problem: with the query SELECT a.*, b.*, c.* FROM t1 a INNER JOIN t2 b USING(name) INNER JOIN t3 c USING(name) WHERE b.field3=7 OR c.field6=16; I only get 1 entry with name=test. By using LEFT JOIN I only get name=test either. For sure, because in table2 there is no test2 entry. The only 2 ways I know to get also test2: 1. INSERT INTO t2 VALUES(test2,NULL,NULL) 2. temporary table But I don't want to have such dummy entries or a temporary table. Does anybody know what I have to do to with: SELECT a.*,b.*,c.* FROM ?? WHERE b.field3=5 OR c.field6=16 the result: namefield1 field2 field3field4 field5 field6 - test 12 5 6 9 10 test 12 5 6 1112 test2 34 NULLNULL 1516 I hope that someone can help me! Thank a lot, Peter __ WEB.DE MyPage - Ultimatives Kommunikationstool! Ihre Message sofort online! Domain aenderbar! http://www.das.ist.aber.ne.lustige.sache.ms/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Jetzt testen für 1 Euro! Ihr All-in-one-Paket! https://digitaledienste.web.de/Club/?mc=021106 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: JOIN-Question
Hi CH! Thanks a lot! That's it! I thought there is no differece between ON (condition) and USING(field), but there is! Thank you very much! CU, Peter [EMAIL PROTECTED] schrieb am 19.09.02 12:02:58: Hi Peter, pls try this query. but its not tested. select t1.name, t1.f1, t1.f2, t2.f3, t2.f4, t3.f5, t3.f6 from t1 left join t2 on (t1.name = t2.name) left join t3 on (t1.name = t3.name) where t2.f3 = 5 or t3.f6 = 16 cheers, CH Peter Stöcker wrote: Hi Mikhail! The query should be released automaticly and it shoulb be something like: SELECT a.*,b.*,c.* FROM ?? WHERE b.field3=5 OR c.field6=16 I want to have all entries wich fit to the condition. But at this time I don't know weather there is a entry with name=test or test2 or not. So when there is none it should return some NULLs otherwise the entries. I already tries using LEFT JOINs, but it doesn't work the way I want it to. Do you think there is a proper way to solve this problem? CU, Peter Mikhail Entaltsev [EMAIL PROTECTED] schrieb am 19.09.02 11:36:16: Peter, If you would like to get such resultset name field1 field2 field3 field4 field5 field6 - test 1 2 5 6 9 10 test 1 2 5 6 11 12 test2 3 4 NULL NULL 15 16 then you need to use left join for t2, but based only on these information I couldn't help you to implement this query (I don't know how to group by rows). Please, give more information about why do you need exactly this resultset. Best regards, Mikhail. - Original Message - From: Peter Stöcker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 11:03 AM Subject: JOIN-Question Hi there! I have a question on JOINs. First the system: table1: name: t1 entries: name field1 field2 test 1 2 test2 3 4 table2: name t2 entries: name field3 field4 test 5 6 test 7 8 table3: name t3 entries: name field5 field6 test 9 10 test 11 12 test2 13 14 test2 15 16 And here the problem: with the query SELECT a.*, b.*, c.* FROM t1 a INNER JOIN t2 b USING(name) INNER JOIN t3 c USING(name) WHERE b.field3=7 OR c.field6=16; I only get 1 entry with name=test. By using LEFT JOIN I only get name=test either. For sure, because in table2 there is no test2 entry. The only 2 ways I know to get also test2: 1. INSERT INTO t2 VALUES(test2,NULL,NULL) 2. temporary table But I don't want to have such dummy entries or a temporary table. Does anybody know what I have to do to with: SELECT a.*,b.*,c.* FROM ?? WHERE b.field3=5 OR c.field6=16 the result: name field1 field2 field3 field4 field5 field6 - test 1 2 5 6 9 10 test 1 2 5 6 11 12 test2 3 4 NULL NULL 15 16 I hope that someone can help me! Thank a lot, Peter __ WEB.DE MyPage - Ultimatives Kommunikationstool! Ihre Message sofort online! Domain aenderbar! http://www.das.ist.aber.ne.lustige.sache.ms/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Jetzt testen für 1 Euro! Ihr All-in-one-Paket! https://digitaledienste.web.de/Club/?mc=021106 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Nur ein Zuhause im Internet: Verwalten Sie alle Ihre E-Mail-Adressen einfach bei WEB.DE FreeMail! http://freemail.web.de/?mc=021124 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: JOIN-Question
Peter, Try this query: SELECT a.name, a.field1, a.field2, b.field3, b.field4,c.field5, c.field6 FROM t1 as a left join t2 as b on (a.name = b.name) left join t3 as c on (a.name = c.name) having b.field3=5 OR c.field6=16 Best regards, Mikhail. - Original Message - From: Peter Stöcker [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 11:56 AM Subject: Re: Re: JOIN-Question Hi Mikhail! The query should be released automaticly and it shoulb be something like: SELECT a.*,b.*,c.* FROM ?? WHERE b.field3=5 OR c.field6=16 I want to have all entries wich fit to the condition. But at this time I don't know weather there is a entry with name=test or test2 or not. So when there is none it should return some NULLs otherwise the entries. I already tries using LEFT JOINs, but it doesn't work the way I want it to. Do you think there is a proper way to solve this problem? CU, Peter Mikhail Entaltsev [EMAIL PROTECTED] schrieb am 19.09.02 11:36:16: Peter, If you would like to get such resultset namefield1 field2 field3field4 field5 field6 - test 12 5 6 9 10 test 12 5 6 1112 test2 34 NULLNULL 1516 then you need to use left join for t2, but based only on these information I couldn't help you to implement this query (I don't know how to group by rows). Please, give more information about why do you need exactly this resultset. Best regards, Mikhail. - Original Message - From: Peter Stöcker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 11:03 AM Subject: JOIN-Question Hi there! I have a question on JOINs. First the system: table1: name: t1 entries: name field1 field2 test 1 2 test2 3 4 table2: name t2 entries: name field3 field4 test 5 6 test 7 8 table3: name t3 entries: name field5 field6 test 9 10 test 11 12 test2 13 14 test2 15 16 And here the problem: with the query SELECT a.*, b.*, c.* FROM t1 a INNER JOIN t2 b USING(name) INNER JOIN t3 c USING(name) WHERE b.field3=7 OR c.field6=16; I only get 1 entry with name=test. By using LEFT JOIN I only get name=test either. For sure, because in table2 there is no test2 entry. The only 2 ways I know to get also test2: 1. INSERT INTO t2 VALUES(test2,NULL,NULL) 2. temporary table But I don't want to have such dummy entries or a temporary table. Does anybody know what I have to do to with: SELECT a.*,b.*,c.* FROM ?? WHERE b.field3=5 OR c.field6=16 the result: namefield1 field2 field3field4 field5 field6 - test 12 5 6 9 10 test 12 5 6 1112 test2 34 NULLNULL 1516 I hope that someone can help me! Thank a lot, Peter __ WEB.DE MyPage - Ultimatives Kommunikationstool! Ihre Message sofort online! Domain aenderbar! http://www.das.ist.aber.ne.lustige.sache.ms/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Jetzt testen für 1 Euro! Ihr All-in-one-Paket! https://digitaledienste.web.de/Club/?mc=021106 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
