D
It can be solved using IFNULL and IF:SELECT
e.id,
IFNULL(z.nombre,
IFNULL(d.nombre,'SIN DATOS')) nombre,
IF(z.nombre IS NULL,
IF(d.nombre IS NULL,
'SIN DATOS',
'DESDE TABLA DIRECCION'),
'DESDE TABLA ZONA') origen
FROM escuela e
LEFT JOIN zona z ON e.zona=z.id
LEFT JOIN direccion d ON e.direccion=d.id;
I made a fiddle for you to see it running: http://sqlfiddle.com/#!9/5a97b4/3/0 I created 3 tables and gave them some data to cover all the possibilities:CREATE TABLE IF NOT EXISTS `escuela` (
`id` int(11) NOT NULL,
`direccion` int(11),
`zona` int(11),
PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `escuela` (`id`, `direccion`, `zona`) VALUES
(0, 0, NULL),
(1, NULL, 0),
(2, 1, 1),
(3, NULL, NULL);
CREATE TABLE IF NOT EXISTS `zona` (
`id` int(11) NOT NULL,
`nombre` varchar(25),
PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `zona` (`id`, `nombre`) VALUES
(0, 'Zona Cero'),
(1, 'Zona Uno');
CREATE TABLE IF NOT EXISTS `direccion` (
`id` int(11) NOT NULL,
`nombre` varchar(25),
PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `direccion` (`id`, `nombre`) VALUES
(0, 'Dir. Cero'),
(1, 'Dir. Uno');
What my query does is the following:
Connect the 3 tables using the address codes and school board area.
The possibilities are: there are values only in Address; they exist only in Zone; they exist in the two; they do not exist in either.
That's why I start with IFNULL. ISNULL tries to return the value of the field that is passed as a parameter, but if it detects that it is an NULL, then returns the value indicated in the second parameter.
For example:SELECT IFNULL(NULL,'SEGUNDO PARAMETRO');
It'll return 'SEGUNDO PARAMETRO'SELECT IFNULL('PRIMER PARAMETRO','SEGUNDO PARAMETRO');
It'll be back.With this we already solve what name to bring. It will be ZONA's if it's not NULL there, or DIRECTION's if it's NULL in ZONA but in DIRECTION it's not, or it'll say 'NO DATA' if it's NULL in the two tables.On the other hand, I use IF to tell where information was brought.
IF is more like the traditional IF of any programming language.
Evaluate a condition (first parameter). If it is true, return the second parameter (what the THEN would do in many languages), and if it is false it returns the third (what the ELSE would do in many languages).For example:SELECT IF(TRUE,'PRIMERO','SEGUNDO');
returns 'PRIMERO'SELECT IF(FALSE,'PRIMERO','SEGUNDO');
back 'SEGUNDO'So to show the origin of data evaluates if zone.name is NULL. If it is, then evaluate if the name is NULL. If it is (i.e. the two IF went for the THEN, that is, the two are NULL) then returns 'No DATA'.
If zone.name is NULL but direction.name is not, then it goes for the first THEN and for the second ELSE, and returns 'DESDE TABLA DIRECCION'
If zone.name is NOT NULL, then it goes directly by the first ELSE, and returns 'DESDE TABLA ZONA'The order in which you evaluate this last section is important, because in my example if there are DATA in the TWO TWOs it will work the name of ZONA and it will say that it is from the TABLA ZONA.If for your case it is preferable that, if there is data in the two tables, return the DIRECTION, then you should invest the IFNULL and the IF
