SQL query with two tables

Question

SQL query with two tables

Navigation

#1 by (1 votes)

0

I have two configuration tables, one general and one specific:

table configurations :

codigo        contenido
codigo_ga     UA-GENERICO

table configurations_domains :

dominio_id        idioma    codigo        contenido
1                 es_ES     codigo_ga     UA-DOM1-ES
2                 (NULL)    codigo_ga     UA-DOM2-GENERICO
2                 es_ES     codigo_ga     UA-DOM2-ES

The language and the domain I always have to pass it in the query, but the language (in the specified table) is optional. Example:

For domain 1 in Spanish, "UA-DOM1-ES" should be displayed.
For domain 1 in English, "UA-GENERICO" should be displayed.
For domain 2 in Spanish "UA-DOM-ES".
and for any language in domain 2, "UA-DOM2-GENERICO" should be displayed

Is this possible in a single query and bringing only one value per "code"?

mysql sql

asked by Pythonizo 15.10.2016 в 00:42

source

1 answer

Problems coding a variable in R Avoid sending form if DatePicker is empty

score 1 · Accepted Answer

Even though your structure is not completely clear to me, you can achieve the result with a query like this:

select   coalesce(  (select cd.Contenido
                       from configuraciones_dominios cd
                      where cd.codigo = cfg.codigo
                        and cd.dominio_id = Params.dominio_id
                        and cd.idioma = Params.Idioma
                    )
                  , (select cd.Contenido
                       from configuraciones_dominios cd
                      where cd.codigo = cfg.codigo
                        and cd.dominio_id = Params.dominio_id
                        and cd.idioma is null
                    )
                  , cfg.Contenido) Contenido
   from configuraciones cfg
        cross join (select 1 dominio_id, 'es_ES' idioma) Params
 where cfg.codigo = 'codigo_ga';

I am putting the parameters in the derived table Params, and with that they are only written once. If you vary them, you will see that you get the desired result, I have checked with this SQL Fiddle , and it returns me , for each parameter:

Sitio  Idioma     Resultado
1      es_ES      UA-DOM1-ES
1      en_US      UA-GENERICO
2      es_ES      UA-DOM2-ES
2      en_US      UA-DOM2-GENERICO