List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:February 25 2009 9:58am
Subject:Re: case when then
View as plain text  
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
  SELECT nid AS id, ntitle, nbody FROM table_3 JOIN table_1 ON nid = id
         WHERE pid = 0;

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 =


Joerg Bruehe,  MySQL Build Team,
Sun Microsystems GmbH,   Sonnenallee 1,   D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering     Muenchen: HRB161028

case when thenAli Deniz EREN24 Feb
  • Re: case when thenAli Deniz EREN25 Feb
  • Re: case when thenJoerg Bruehe25 Feb