Hi Ali, all!
Ali Deniz EREN wrote: > table_1 > id pid nid > ----------------------- > 1 6 0 > 2 0 5 > > table_2 (referer -> pid) > id title body > ----------------------- > 1 title1 body_text1 > 2 title2 body_text2 > 3 title3 body_text3 > 4 title4 body_text4 > 5 title5 body_text5 > 6 title6 body_text6 > 7 title7 body_text7 > > table_3 (referer -> nid) > id ntitle nbody > -------------------------- > 1 ntitle1 nbody_text1 > 2 ntitle2 nbody_text2 > 3 ntitle3 nbody_text3 > 4 ntitle4 nbody_text4 > 5 ntitle5 nbody_text5 > 6 ntitle6 nbody_text6 > 7 ntitle7 nbody_text7 > > I want to get values table_2 and table_3 according to table_1. As a result, > it must be as below. > > id title body > ------------------------ > 6 title6 body_text6 > 5 ntitle5 nbody_text5 I assume you want results matching *all* rows in table_1, getting rows from table_2 (if nid is 0) or table_3 (if pid is 0). Specifically, you don't tell whether it is possible that table_1 has rows with both pid=0 and nid=0, or rows with both pid<>0 and nid<>0, and what should happen for these. For now, I assume there are no such rows. > > > What would be the query? Your subject line shows you are treating it as two different cases, using data from table_2 or table_3. This is correct. But rather than trying to decide on a case-by-case basis depending on the values in table_1, you should fully separate them. First, try to come up with a SELECT that will return all relevant data from table_2 for those rows where table_1.nid = 0. This should be something like SELECT pid AS id, title, body FROM table_2 JOIN table_1 ON pid = id WHERE nid = 0; Then, you can do the same for table_3. The statement is quite obvious. Now you could easily use these two statements in sequence. If, however, you want to run this as one statement, you can use UNION: SELECT pid AS id, title, body FROM table_2 JOIN table_1 ON pid = id WHERE nid = 0 UNION SELECT nid AS id, ntitle, nbody FROM table_3 JOIN table_1 ON nid = id WHERE pid = 0; Disclaimers: 1) I didn't test it. 2) As you didn't tell which version you are using (hint!), this answer is generic. 3) Specifically, I didn't check whether the use of "id" as an alias name for the result column would conflict with its use as a table column name in the ON clause. It might be necessary to write ON pid = table_1.id HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org