Alternative column to use in a JOIN in MySQL

Navigation
5

I have a table called liturgia_calendario with the following structure: 

calendario_id     ff_yy      ff_mm      ff_dd      id_liturgia      pre
1                  2018       06         01          198            0
2                  2018       06         02          199            360

And I need to join it with another table called liturgia to use the data of col1, col2, ... . The structure of this table is more or less this: 

id_liturgia     col1      col2...
198                  
199                  
...
360

What I want is that:

  • If the column pre in liturgia_calendario equals 0 , I use the value in column id_liturgia to do the join.

  • If the column pre in liturgia_calendario is different from 0 , I use the value of that column to do the join.

In the case of the 1st row, the join would be done using the value 198 and in the case of the 2nd row, the value 350 would be used.

I can get one or the other value from the liturgia_calendario table. For example, this query works to alternatively get one or another column under the alias id_liturgia : 

SELECT CASE WHEN (pre = 0)
            THEN id_liturgia
            ELSE pre 
       END AS id_liturgia
FROM liturgia_calendario 
WHERE f_yy=2018 and f_mm=6;

Result: 

id_liturgia
198
360

But when I try it with the JOIN it does not work for me.

If I write the query like this: 

SELECT CASE WHEN (pre = 0)
            THEN id_liturgia
            ELSE pre 
       END AS id_liturgia
FROM liturgia_calendario lc
INNER JOIN liturgia l ON lc.id_liturgia=l.id_liturgia
WHERE lc.f_yy=2018 and lc.f_mm=6;

I get the error:

  

Column 'id_liturgia' in field list is ambiguous

If I give another alias to the column resulting from the CASE I get the same error: 

SELECT CASE WHEN (pre = 0)
            THEN id_liturgia
            ELSE pre 
       END AS id_liturgiaz
FROM liturgia_calendario lc
INNER JOIN liturgia l ON lc.id_liturgiaz=l.id_liturgia
WHERE lc.f_yy=2018 and lc.f_mm=6;

I do not know why this happens or if there is a better way to achieve what I want.

    
asked by A. Cedano 02.06.2018 в 14:10
source

2 answers

3

Simply enter the condition in the JOIN / WHERE: 

SELECT l.id_liturgia
  FROM liturgia_calendario lc
  INNER JOIN liturgia l
    ON ((lc.pre = 0 AND lc.id_liturgia = l.id_liturgia) OR lc.pre = l.id_liturgia)
  WHERE lc.f_yy=2018 and lc.f_mm=6;

Example

    
answered by 02.06.2018 / 15:25
source
3

The two problems you have come from the same origin: the aliases of columns and how they are processed by the engine:

  • When you use the same column name as alias as part of the JOIN , the engine does not identify what you mean as there are two tables with the same column name and by not indicating the alias of the table tells you that the name is ambiguous.

  • When you use another name as alias, the query does not work for you, because the column aliases, the engine "materializes" them well at the end of the query process. That is, in the instance of JOIN , id_liturgiaz does not exist.

The possible solution is to use a subquery to materialize the new alias: 

SELECT  *
    FROM ( SELECT   CASE WHEN (pre = 0)
                        THEN id_liturgia
                        ELSE pre 
                   END AS id_liturgia
                   FROM liturgia_calendario 
                   WHERE f_yy=2018 and f_mm=6
    ) lc
    INNER JOIN liturgia l 
        ON lc.id_liturgia=l.id_liturgia

Either repeating CASE in ON 

SELECT l.id_liturgia
FROM liturgia_calendario lc
INNER JOIN liturgia l 
    ON l.id_liturgia=(CASE WHEN (lc.pre = 0) THEN lc.id_liturgia ELSE lc.pre END)
WHERE lc.f_yy=2018 and lc.f_mm=6;
    
answered by 02.06.2018 в 15:24
Avoid temporary copies in call to lambdas I would like to apply for phone numbers in MySQL