[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: case when then
From:       Joerg Bruehe <Joerg.Bruehe () Sun ! COM>
Date:       2009-02-25 9:58:54
Message-ID: 49A5165E.5070606 () Sun ! COM
[Download RAW message or body]

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.Bruehe@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=mysql-marcsub@progressive-comp.com


[prev in list] [next in list] [prev in thread] [next in thread]