domingo, 5 de mayo de 2013

65 - Varias tablas (right join)



"right join" opera del mismo modo que "left join" sólo que la búsqueda de coincidencias la realiza de modo inverso, es decir, los roles de las tablas se invierten, busca coincidencia de valores desde la tabla de la derecha en la tabla de la izquierda y si un valor de la tabla de la derecha no encuentra coincidencia en la tabla de la izquierda, se genera una fila extra (una por cada valor no encontrado) con todos los campos seteados a "null".

Trabajamos con las tablas de una librería:

-libros: codigo (clave primaria), titulo, autor, codigoeditorial, precio, 
cantidad y
-editoriales: codigo (clave primaria), nombre.
 
Estas sentencias devuelven el mismo resultado:

 select nombre,titulo
  from editoriales as e
  left join libros as l
  on e.codigo=l.codigoeditorial;
 
 select nombre,titulo
  from libros as l
  right join editoriales as e
  on e.codigo=l.codigoeditorial;
 
La primera busca valores de "codigo" de la tabla "editoriales" (tabla de la izquierda) coincidentes con los valores de "codigoeditorial" de la tabla "libros" (tabla de la derecha). La segunda busca valores de la tabla de la derecha coincidentes con los valores de la tabla de la izquierda.



PROBLEMA RESUELTO



Trabajamos con las tablas "libros" y editoriales" de una librería.
Eliminamos dichas tablas, si existen:

 drop table if exists libros, editoriales;
 
Creamos las siguientes tablas:

 create table libros(
  codigo int unsigned auto_increment,
  titulo varchar(40) not null,
  autor varchar(30) not null default 'Desconocido',
  codigoeditorial tinyint unsigned not null,
  precio decimal(5,2) unsigned,
  cantidad tinyint unsigned default 0,
  primary key (codigo)
 );
 
 create table editoriales(
  codigo tinyint unsigned auto_increment,
  nombre varchar(20) not null,
  primary key(codigo)
 );
 
Cargamos algunos registros en la tabla "editoriales":

 insert into editoriales (nombre) values('Paidos');
 insert into editoriales (nombre) values('Emece');
 insert into editoriales (nombre) values('Planeta');
 insert into editoriales (nombre) values('Sudamericana');
 
Cargamos algunos registros en la tabla "libros":

 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)
  values('El Aleph','Borges',3,43.5,200);
 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)
  values('Alicia en el pais de las maravillas','Lewis Carroll',2,33.5,100);
 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)
  values('Aprenda PHP','Mario Perez',1,55.8,50);
 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)
  values('Java en 10 minutos','Juan Lopez',1,88,150);
 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)
  values('Alicia a traves del espejo','Lewis Carroll',1,15.5,80);
 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)
  values('Cervantes y el quijote','Borges- Bioy Casares',3,25.5,300);
 
Necesitamos los títulos y nombres de las editoriales de los libros, incluso de aquellos editoriales 
que no tienen libros:

 select nombre,titulo
  from editoriales as e
  left join libros as l
  on e.codigo=l.codigoeditorial;
 
Esta sentencia busca los nombres de las editoriales que están presentes en "libros". Podemos 
realizar la búsqueda de modo inverso con "right join":

 select nombre,titulo
  from libros as l
  right join editoriales as e
  on e.codigo=l.codigoeditorial;
 
La primera, con "left join", busca valores de "codigo" de la tabla "editoriales" (tabla de la izquierda) coincidentes con los valores de "codigoeditorial" de la tabla "libros" (tabla de la derecha). La segunda, con "right join", busca valores de "codigoeditorial" de la tabla "libros" (tabla de la derecha) coincidentes con los valores de "codigo" de la tabla "editoriales" (tabla de la izquierda).



PROBLEMA PROPUESTO


Un club dicta clases de distintos deportes. En una tabla llamada "socios" 
guarda los datos de sus socios y en una tabla denominada "inscriptos" almacena 
la información necesaria para las inscripciones de los socios a los distintos 
deportes.
 
1- Elimine las tablas si existen.
 
2- Cree las tablas:
 
 create table socios(
  documento char(8) not null,
  nombre varchar(30),
  domicilio varchar(30),
  primary key(documento)
 );
 
 create table inscriptos(
  documento char(8) not null, 
  deporte varchar(15) not null,
  año year,
  matricula char(1), /*si esta paga ='s' sino 'n'*/
  primary key(documento,deporte,año)
 );
 
3- Ingrese algunos registros para ambas tablas:
 
 insert into socios values('22333444','Juan Perez','Colon 234');
 insert into socios values('23333444','Maria Lopez','Sarmiento 465');
 insert into socios values('24333444','Antonio Juarez','Caseros 980');
 insert into socios values('25333444','Marcelo Pereyra','Sucre 349');
 
 insert into inscriptos values ('22333444','natacion','2005','s');
 insert into inscriptos values ('22333444','natacion','2006','n');
 insert into inscriptos values ('23333444','natacion','2005','s');
 insert into inscriptos values ('23333444','tenis','2006','s');
 insert into inscriptos values ('23333444','natacion','2006','s');
 insert into inscriptos values ('24333444','tenis','2006','n');
 insert into inscriptos values ('24333444','basquet','2006','n');
 
4- Realice un "left join" de la tabla "socios" a "inscriptos" buscando 
coincidencia de "documento":
  
 
Note que el socio que no está inscripto en ningún deporte tiene la fila 
seteada a "null".
 
5- Realice un "right join" para obtener la misma salida anterior:
  
 
6- Ingrese una inscripción de alguien que no sea socio (documento que no 
se encuentre en la tabla "socios"):
 
 insert into inscriptos values ('26333444','basquet','2006','n');
 
7- Realice un "right join" desde la tabla "socios" a "inscriptos" buscando 
coincidencia de documento:

 
 
Note que la persona con documento "26333444" no se encuentra en "socios", 
la columna "nombre" (correspondiente a la tabla "socios") contiene "null".





64 - Varias tablas (left join)



Hemos visto cómo usar registros de una tabla para encontrar registros de otra tabla, uniendo ambas tablas con "join" y enlazándolas con una condición "on" en la cual colocamos el campo en común. O sea, hacemos un "join" y asociamos registros de 2 tablas usando el "on", buscando coincidencia en los valores del campo que tienen en comun ambas tablas.

Trabajamos con las tablas de una librería:

-libros: codigo (clave primaria), titulo, autor, codigoeditorial, precio, cantidad y
-editoriales: codigo (clave primaria), nombre.
 
Queremos saber de qué editoriales no tenemos libros.

Para averiguar qué registros de una tabla no se encuentran en otra tabla necesitamos usar un "join" diferente.

Necesitamos determinar qué registros no tienen correspondencia en otra tabla, cuáles valores de la primera tabla (de la izquierda) no están en la segunda (de la derecha).

Para obtener la lista de editoriales y sus libros, incluso de aquellas editoriales de las cuales no tenemos libros usamos:

 select * from editoriales
  left join libros
  on editoriales.codigo=libros.codigoeditorial;
 
Un "left join" se usa para hacer coincidir registros en una tabla (izquierda) con otra tabla (derecha), pero, si un valor de la tabla de la izquierda no encuentra coincidencia en la tabla de la derecha, se genera una fila extra (una por cada valor no encontrado) con todos los campos seteados a "null".

Entonces, la sintaxis es la siguiente: se nombran ambas tablas, una a la izquierda del "join" y la otra a la derecha, y la condición para enlazarlas, es decir, el campo por el cual se combinarán, se establece luego de "on". Es importante la posición en que se colocan las tablas en un "left join", la tabla de la izquierda es la que se usa para localizar registros en la tabla de la derecha. Por lo tanto, estos "join" no son iguales:

 select * from editoriales
 left join libros
 on editoriales.codigo=libros.codigoeditorial;
 
 select * from libros
  left join editoriales
  on editoriales.codigo=libros.codigoeditorial;
 
La primera sentencia opera así: por cada valor de codigo de "editoriales" busca coincidencia en la tabla "libros", si no encuentra coincidencia para algún valor, genera una fila seteada a "null".

La segunda sentencia opera de modo inverso: por cada valor de "codigoeditorial" de "libros" busca coincidencia en la tabla "editoriales", si no encuentra coincidencia, setea la fila a "null".

Usando registros de la tabla de la izquierda se encuentran registros en la tabla de la derecha.

Luego del "on" se especifican los campos que se asociarán; no se deben colocar condiciones en la parte "on" para restringir registros que deberían estar en el resultado, para ello hay que usar la cláusula "where".

Un "left join" puede tener clausula "where" que restringa el resultado de la consulta considerando solamente los registros que encuentran coincidencia en la tabla de la derecha:

 select e.nombre,l.titulo
  from editoriales as e
  left join libros as l
  on e.codigo=l.codigoeditorial
  where l.codigoeditorial is not null;
 
El anterior "left join" muestra los valores de la tabla "editoriales" que están presentes en la tabla de la derecha ("libros").

También podemos mostrar las editoriales que no están presentes en "libros":

 select e.nombre,l.titulo from editoriales as e
  left join libros as l
  on e.codigo=l.codigoeditorial
  where l.codigoeditorial is null;
 
El anterior "left join" muestra los valores de la tabla "editoriales" que no encuentran correspondencia en la tabla de la derecha, "libros".


PROBLEMA RESUELTO



Trabajamos con las tablas "libros" y editoriales" de una librería.

Eliminamos dichas tablas, si existen:

 drop table if exists libros, editoriales;
 
Creamos las siguientes tablas:

 create table libros(
  codigo int unsigned auto_increment,
  titulo varchar(40) not null,
  autor varchar(30) not null default 'Desconocido',
  codigoeditorial tinyint unsigned not null,
  precio decimal(5,2) unsigned,
  cantidad tinyint unsigned default 0,
  primary key (codigo)
 );
 
 create table editoriales(
  codigo tinyint unsigned auto_increment,
  nombre varchar(20) not null,
  primary key(codigo)
 );
 
Cargamos algunos registros en la tabla "editoriales":

 insert into editoriales (nombre) values('Paidos');
 insert into editoriales (nombre) values('Emece');
 insert into editoriales (nombre) values('Planeta');
 insert into editoriales (nombre) values('Sudamericana');
 
Cargamos algunos registros en la tabla "libros":

 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)
  values('El Aleph','Borges',3,43.5,200);
 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)
  values('Alicia en el pais de las maravillas','Lewis Carroll',2,33.5,100);
 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)
  values('Aprenda PHP','Mario Perez',1,55.8,50);
 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)
  values('Java en 10 minutos','Juan Lopez',1,88,150);
 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)
  values('Alicia a traves del espejo','Lewis Carroll',1,15.5,80);
 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)
  values('Cervantes y el quijote','Borges- Bioy Casares',3,25.5,300);
 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)
  values('Aprenda Java en 10 minutos','Lopez Juan',5,28,100);
 
Para obtener la lista de todas las editoriales y los libros de las mismas, incluso de las cuales 
no tenemos libros usamos:

 select * from editoriales
  left join libros
  on editoriales.codigo=libros.codigoeditorial;
 
Un "left join" se usa para hacer coincidir registros en una tabla (izquierda) con otra tabla (derecha), 
pero, si un valor de la tabla de la izquierda no encuentra coincidencia en la tabla de la derecha, 
se genera una fila extra (una por cada valor no encontrado) con todos los campos seteados a "null".

Recuerde que es importante la posición en que se colocan las tablas en un "left join", la tabla de la 
izquierda es la que localiza registros en la tabla de la derecha. Por lo tanto, estos "join" no son iguales:

 select * from editoriales
 left join libros
 on editoriales.codigo=libros.codigoeditorial;
 
 select * from libros
  left join editoriales
  on editoriales.codigo=libros.codigoeditorial;
 
La primera sentencia busca coincidencia en la tabla "libros" por cada valor de codigo de "editoriales", 
si no encuentra coincidencia para algún valor, genera una fila seteada a "null". No hay libros de la 
editorial "Sudamericana", entonces esa fila contiene "null" en todos los campos correspondientes 
a "libros".

La segunda sentencia busca coincidencia en la tabla "editoriales" por cada valor de 
"codigoeditorial" de "libros", si no encuentra coincidencia para algún valor, genera una 
fila seteada a "null". El libro "Aprenda java en 10 minutos" tiene código de editorial "5", 
valor inexistente en la tabla "editoriales", por ello, esa fila contiene "null" en 
todos los campos correspondientes a "editoriales".

Para encontrar los valores de código de la tabla "editoriales" que están presentes en la tabla 
"libros" usamos un "where":

 select nombre,titulo
  from editoriales as e
  left join libros as l
  on e.codigo=l.codigoeditorial
  where l.codigoeditorial is not null;
 
Para mostrar las editoriales que no están presentes en "libros":

 select e.nombre,l.titulo from editoriales as e
  left join libros as l
  on e.codigo=l.codigoeditorial
  where l.codigoeditorial is null;
 
 
 
PROBLEMA PROPUESTO 


Una empresa tiene registrados sus clientes en una tabla llamada 
"clientes", también tiene una tabla "provincias" donde registra 
los nombres de las provincias.
 
1- Elimine las tablas "clientes" y "provincias", si existen:
 
2- Créelas con las siguientes estructuras:
 
 create table clientes (
  codigo int unsigned auto_increment,
  nombre varchar(30) not null,
  domicilio varchar(30),
  ciudad varchar(20),
  codigoProvincia tinyint unsigned,
  telefono varchar(11),
  primary key(codigo)
 );
 
 create table provincias(
  codigo tinyint unsigned auto_increment,
  nombre varchar(20),
  primary key (codigo)
 );
 
3- Ingrese algunos registros para ambas tablas:
 
 insert into provincias (nombre) values('Cordoba');
 insert into provincias (nombre) values('Santa Fe');
 insert into provincias (nombre) values('Corrientes');
 insert into provincias (nombre) values('Misiones');
 insert into provincias (nombre) values('Salta');
 insert into provincias (nombre) values('Buenos Aires');
 insert into provincias (nombre) values('Neuquen');
 
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Lopez Marcos', 'Colon 111', 'Córdoba',1,'null');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Perez Ana', 'San Martin 222', 'Cruz del Eje',1,'4578585');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Garcia Juan', 'Rivadavia 333', 'Villa Maria',1,'4578445');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Perez Luis', 'Sarmiento 444', 'Rosario',2,null);
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Pereyra Lucas', 'San Martin 555', 'Cruz del Eje',1,'4253685');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Gomez Ines', 'San Martin 666', 'Santa Fe',2,'0345252525');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Torres Fabiola', 'Alem 777', 'Villa del Rosario',1,'4554455');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Lopez Carlos', 'Irigoyen 888', 'Cruz del Eje',1,null);
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Ramos Betina', 'San Martin 999', 'Cordoba',1,'4223366');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Lopez Lucas', 'San Martin 1010', 'Posadas',4,'0457858745');
 
4- Queremos saber de qué provincias no tenemos clientes:

  
5- Queremos saber de qué provincias si tenemos clientes, sin repetir el 
nombre de la provincia:

  
6- Omita la referencia a las tablas en la condición "on" para verificar 
que la sentencia no se ejecuta porque el nombre del campo "codigo" es 
ambiguo (ambas tablas lo tienen):
Otros problemas:
A) Un club dicta clases de distintos deportes. En una tabla llamada 
"socios" guarda los datos de sus socios y en una tabla denominada "inscriptos" 
almacena la información necesaria para las inscripciones de los socios a los 
distintos deportes.
 
1- Elimine las tablas si existen.
 
2- Cree las tablas:
 
 create table socios(
  documento char(8) not null,
  nombre varchar(30),
  domicilio varchar(30),
  primary key(documento)
 );
 
 create table inscriptos(
  documento char(8) not null, 
  deporte varchar(15) not null,
  año year,
  matricula char(1), /*si esta paga ='s' sino 'n'*/
  primary key(documento,deporte,año)
 );
 
3- Ingrese algunos registros para ambas tablas:
 
 insert into socios values('22333444','Juan Perez','Colon 234');
 insert into socios values('23333444','Maria Lopez','Sarmiento 465');
 insert into socios values('24333444','Antonio Juarez','Caseros 980');
 insert into socios values('25333444','Ana Juarez','Sucre 134');
 insert into socios values('26333444','Sofia Herrero','Avellaneda 1234');
 
 insert into inscriptos values ('22333444','natacion','2005','s');
 insert into inscriptos values ('22333444','natacion','2006','n');
 insert into inscriptos values ('23333444','natacion','2005','s');
 insert into inscriptos values ('23333444','tenis','2006','s');
 insert into inscriptos values ('23333444','natacion','2006','s');
 insert into inscriptos values ('25333444','tenis','2006','n');
 insert into inscriptos values ('25333444','basquet','2006','n');
 
4- Muestre el nombre del socio, deporte y año realizando un join:
  
5- Muestre los nombres de los socios que no se han inscripto nunca en un 
deporte:

 
6- Omita la referencia a las tablas en la condición "on" para verificar 
que la sentencia no se ejecuta porque el nombre del campo "documento" es 
ambiguo (ambas tablas lo tienen):
 
 
B) Un club de barrio realiza una rifa anual y guarda los datos de las rifas 
en dos tablas, una denominada "premios" y otra llamada "numerosrifa".
 
1- Elimine las tablas si existen.
 
2- Cree las tablas:
 create table premios(
  posicion tinyint unsigned auto_increment,
  premio varchar(20),
  numeroganador tinyint unsigned,
  primary key(posicion)
 );
 
 create table numerosrifa(
  numero tinyint unsigned not null,
  documento char(8) not null,
  primary key(numero)
 );
 
3- Ingrese algunos registros:
 
 insert into premios values(1,'PC Pentium',205);
 insert into premios values(2,'Televisor 21 pulgadas',29);
 insert into premios values(3,'Microondas',5);
 insert into premios values(4,'Multiprocesadora',15);
 insert into premios values(5,'Cafetera',33);
 
 insert into numerosrifa values(205,'22333444');
 insert into numerosrifa values(200,'23333444');
 insert into numerosrifa values(5,'23333444');
 insert into numerosrifa values(8,'23333444');
 insert into numerosrifa values(1,'24333444');
 insert into numerosrifa values(109,'28333444');
 insert into numerosrifa values(15,'30333444');
 insert into numerosrifa values(29,'29333444');
 insert into numerosrifa values(28,'32333444');
 
4- Muestre todos los números de rifas vendidos ("numerosrifas") y realice un 
"left join" mostrando la posición y el premio:
   
note que la posición "5" no aparece en la lista porque el número ganador de 
esa posición no fue vendido, no se encuentra en la tabla "premios". Y note 
que los números vendidos que no ganaron tiene la fila seteada a "null".
 
5- Muestre los mismos datos anteriores pero teniendo en cuenta los números 
ganadores solamente:

 
6- Realice un "left join" pero en esta ocasión busque los números ganadores 
de la tabla "premios" en la tabla "numerosrifa":

 
Note que el premio de la posición "5" no encuentra coincidencia en la tabla 
"numerosrifa" (porque no fue vendido) y el campo está seteado a "null".
 
7- Realice el mismo "join" anterior pero sin considerar los valores de 
"premios" que no encuentren coincidencia en "numerosrifa".
 
 


63 - Clave foránea.

Un campo que se usa para establecer un "join" (unión) con otra tabla en la cual es clave primaria, se denomina "clave ajena o foránea".

En el ejemplo de la librería en que utilizamos las tablas "libros" y "editoriales" con los campos:

 libros: codigo (clave primaria), titulo, autor, codigoeditorial, precio, cantidad y
 editoriales: codigo (clave primaria), nombre.
 
el campo "codigoeditorial" de "libros" es una clave foránea, se emplea para enlazar la tabla "libros" con "editoriales" y es clave primaria en "editoriales" con el nombre "codigo".

Cuando alteramos una tabla, debemos tener cuidado con las claves foráneas. Si modificamos el tipo, longitud o atributos de una clave foránea, ésta puede quedar inhabilitada para hacer los enlaces.

Las claves foráneas y las claves primarias deben ser del mismo tipo para poder enlazarse. Si modificamos una, debemos modificar la otra para que los valores se correspondan.


PROBLEMA RESUELTO



Trabajamos con las tablas "libros" y "editoriales" de una librería.
Eliminamos las tablas, si existen:

 drop table libros, editoriales;
 
Creamos las tablas:

 create table libros(
  codigo int unsigned auto_increment,
  titulo varchar(40) not null,
  autor varchar(30) not null default 'Desconocido',
  codigoeditorial tinyint unsigned not null,
  precio decimal(5,2) unsigned,
  cantidad smallint unsigned default 0,
  primary key (codigo)
 );
 
 create table editoriales(
  codigo tinyint unsigned auto_increment,
  nombre varchar(20) not null,
  primary key(codigo)
 );
 
En este ejemplo, el campo "codigoeditorial" de "libros" es una clave foránea, se emplea para 
enlazar la tabla "libros" con "editoriales".

Ingresamos algunos registros:

 insert into editoriales values(2,'Emece');
 insert into editoriales values(15,'Planeta');
 insert into editoriales values(23,'Paidos');
 
 insert into libros values(1,'El aleph','Borges',23,4.55,10);
 insert into libros values(2,'Alicia en el pais de las maravillas','Lewis Carroll'
                           ,2,11.55,2);
 insert into libros values(3,'Martin Fierro','Jose Hernandez',15,7.12,4);
 
Si modificamos el tipo, longitud o atributos de una clave foránea, ésta puede quedar inhabilitada para hacer los enlaces.

Veamos un ejemplo:

 alter table libros
  modify codigoeditorial char(1);
 
Veamos cómo afectó el cambio a la tabla "libros":

 select * from libros;
 
El libro con código de editorial "23" ("Paidos") ahora tiene "2" ("Emece") en "codigoeditorial" y
el libro con código de editorial "15" ("Planeta") ahora almacena "1" (valor inexistente en "editoriales").

Si buscamos coincidencia de códigos en la tabla "editoriales":

 select l.titulo,e.nombre
  from libros as l
  join editoriales as e
  on l.codigoeditorial=e.codigo;
 
El resultado es erróneo.

Las claves foráneas y las claves primarias deben ser del mismo tipo para poder enlazarse. 
Si modificamos una, debemos modificar la otra para que los valores se correspondan.
Intentemos modificar la clave en "editoriales":

 alter table editoriales
  modify codigo char(1);
 
No lo permite porque si la modifica los valores para el campo clave quedan repetidos.


PROBLEMA PROPUESTO



Una empresa tiene registrados sus clientes en una tabla llamada "clientes", 
también tiene una tabla "provincias" donde registra los nombres de las 
provincias.
 
1- Elimine la tabla "clientes" y "provincias", si existen:
 
  
2- Créelas con las siguientes estructuras:
 
 create table clientes (
  codigo int unsigned auto_increment,
  nombre varchar(30) not null,
  domicilio varchar(30),
  ciudad varchar(20),
  codigoprovincia tinyint unsigned,
  telefono varchar(11),
  primary key(codigo)
 );
 
 create table provincias(
  codigo tinyint unsigned auto_increment,
  nombre varchar(20),
  primary key (codigo)
 );
 
En este ejemplo, el campo "codigoprovincia" de "clientes" es una clave 
foránea, se emplea para enlazar la tabla "clientes" con "provincias".
 
3- Ingrese algunos registros para ambas tablas:
 
 insert into provincias(codigo,nombre) values(1,'Cordoba');
 insert into provincias(codigo,nombre) values(2,'Santa Fe');
 insert into provincias(codigo,nombre) values(30,'Misiones');
 insert into provincias(codigo,nombre) values(13,'Salta');
 insert into provincias(codigo,nombre) values(15,'Buenos Aires');
 insert into provincias(codigo,nombre) values(20,'Neuquen');
 
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Lopez Marcos', 'Colon 111', 'Córdoba',1,'null');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Perez Ana', 'San Martin 222', 'Cruz del Eje',1,'4578585');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Garcia Juan', 'Rivadavia 333', 'Villa Maria',1,'4578445');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Perez Luis', 'Sarmiento 444', 'Rosario',2,null);
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Ramos Betina', 'San Martin 999', 'Bahia Blanca',15,'4223366');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Lopez Lucas', 'San Martin 1010', 'Posadas',30,'0457858745');
 
4- Enlace las tablas:
 
5- Modifique el campo "codigoprovincia" a "char(1)":
  
6- Vea cómo afectó el cambio a la tabla "clientes":
 
 
El cliente de "Bahia Blanca" con código de provincia "15" ("Buenos Aires") 
ahora tiene "1" ("Cordoba") y el cliente con código de provincia "30" 
("Misiones") ahora almacena "3" (valor inexistente en "provincias").
 
7- Realice un "left join" buscando coincidencia de códigos en la tabla 
"provincias":
 
 El resultado es erróneo.
 
8- Intente modificar la clave primaria en "provincias" para que se corresponda 
con "codigoprovincia" de "clientes":
  
 
No lo permite porque si la modifica los valores para el campo clave quedan repetidos.


Otros problemas:

Un club dicta clases de distintos deportes. En una tabla llamada "deportes" 
guarda la información de los distintos deportes que se enseñan y en una tabla
denominada "inscriptos" almacena la información necesaria para las inscripciones 
a los distintos deportes.
 
1- Elimine las tablas si existen.
 
2- Cree las tablas:
 
 create table deportes(
  codigo tinyint unsigned,
  nombre varchar(20),
  profesor varchar(30),
  primary key(codigo)
 );
 
 create table inscriptos(
  documento char(8) not null, 
  codigodeporte tinyint unsigned not null,
  año year,
  matriculapaga char(1),/* 's' si está paga, 'n' si no está paga*/
  primary key(documento,codigodeporte,año)
 );
 
3- Ingrese algunos registros para ambas tablas:
 
 insert into deportes values(1,'Tenis','Juan Lopez');
 insert into deportes values(2,'Natacion','Maria Lopez');
 insert into deportes values(3,'Basquet','Antonio Juarez');
 
 insert into inscriptos values ('22333444',2,'2005','s');
 insert into inscriptos values ('22333444',2,'2006','n');
 insert into inscriptos values ('23333444',2,'2005','s');
 insert into inscriptos values ('23333444',1,'2005','s');
 insert into inscriptos values ('23333444',1,'2006','s');
 insert into inscriptos values ('24333444',2,'2006','n');
 insert into inscriptos values ('24333444',3,'2006','n');
 
4- Muestre el nombre del deporte y todos los campos de la tabla "inscriptos":
  
5- Modifique el campo "codigo" de "deportes" para que almacene 1 caracter:
 
  
6- Actualice la tabla "deportes" almacenando en "codigo" el primer caracter 
del nombre del deporte:
   
7- Vea cómo cambió la tabla:
 
8- Realice un "join":
  
No encuentra coincidencia.




62 - Varias tablas (join)

Hasta ahora hemos trabajado con una sola tabla, pero en general, se trabaja con varias tablas.

Para evitar la repetición de datos y ocupar menos espacio, se separa la información en varias tablas. Cada tabla tendrá parte de la información total que queremos registrar.

Por ejemplo, los datos de nuestra tabla "libros" podrían separarse en 2 tablas, una "libros" y otra "editoriales" que guardará la información de las editoriales. En nuestra tabla "libros" haremos referencia a la editorial colocando un código que la identifique. Veamos:

 create table libros(
  codigo int unsigned auto_increment,
  titulo varchar(40) not null,
  autor varchar(30) not null default 'Desconocido',
  codigoeditorial tinyint unsigned not null,
  precio decimal(5,2) unsigned,
  cantidad smallint unsigned default 0,
  primary key (codigo)
 );

 create table editoriales(
  codigo tinyint unsigned auto_increment,
  nombre varchar(20) not null,
  primary key(codigo)
 );
 
De este modo, evitamos almacenar tantas veces los nombres de las editoriales en la tabla "libros" y guardamos el nombre en la tabla "editoriales"; para indicar la editorial de cada libro agregamos un
campo referente al código de la editorial en la tabla "libros" y en "editoriales".

Al recuperar los datos de los libros:

 select * from libros;
 
vemos que en el campo "editorial" aparece el código, pero no sabemos el nombre de la editorial. Para obtener los datos de cada libro, incluyendo el nombre de la editorial, necesitamos consultar ambas tablas, traer información de las dos.

Cuando obtenemos información de más de una tabla decimos que hacemos un "join" (unión). Veamos un ejemplo:

 select * from libros
  join editoriales
  on libros.codigoeditorial=editoriales.codigo;
 
Analicemos la consulta anterior.

Indicamos el nombre de la tabla luego del "from" ("libros"), unimos esa tabla con "join" y el nombre de la otra tabla ("editoriales"), luego especificamos la condición para enlazarlas con "on", es decir, el campo por el cual se combinarán. "on" hace coincidir registros de las dos tablas basándose en el valor de algún campo, en este ejemplo, los códigos de las editoriales de ambas tablas, el campo "codigoeditorial" de "libros" y el campo "codigo" de "editoriales" son los que enlazarán ambas tablas.

Cuando se combina (join, unión) información de varias tablas, es necesario indicar qué registro de una tabla se combinará con qué registro de la otra tabla.

Si no especificamos por qué campo relacionamos ambas tablas, por ejemplo:

 select * from libros
  join editoriales;
 
el resultado es el producto cartesiano de ambas tablas (cada registro de la primera tabla se combina con cada registro de la segunda tabla), un "join" sin condición "on" genera un resultado en el que aparecen todas las combinaciones de los registros de ambas tablas. La información no sirve.

Note que en la consulta

 select * from libros
  join editoriales
  on libros.codigoeditorial=editoriales.codigo;
 
al nombrar el campo usamos el nombre de la tabla también. Cuando las tablas referenciadas tienen campos con igual nombre, esto es necesario para evitar confusiones y ambiguedades al momento de referenciar un campo. En este ejemplo, si no especificamos "editoriales.codigo" y solamente tipeamos "codigo", MySQL no sabrá si nos referimos al campo "codigo" de "libros" o de "editoriales".

Si omitimos la referencia a las tablas al nombrar el campo "codigo" (nombre de campo que contienen ambas tablas):

 select * from libros
  join editoriales
  on codigoeditorial=codigo;
 
aparece un mensaje de error indicando que "codigo" es ambiguo.

Entonces, si en las tablas, los campos tienen el mismo nombre, debemos especificar a cuál tabla pertenece el campo al hacer referencia a él, para ello se antepone el nombre de la tabla al nombre del campo, separado por un punto (.).

Entonces, se nombra la primer tabla, se coloca "join" junto al nombre de la segunda tabla de la cual obtendremos información y se asocian los registros de ambas tablas usando un "on" que haga coincidir los valores de un campo en común en ambas tablas, que será el enlace.

Para simplificar la sentencia podemos usar un alias para cada tabla:

 select * from libros as l
  join editoriales as e
  on l.codigoeditorial=e.codigo;
 
Cada tabla tiene un alias y se referencian los campos usando el alias correspondiente. En este ejemplo, el uso de alias es para fines de simplificación, pero en algunas consultas es absolutamente necesario.

En la consulta anterior vemos que el código de la editorial aparece 2 veces, desde la tabla "libros" y "editoriales". Podemos solicitar que nos muestre algunos campos:

 select titulo,autor,nombre from libros as l
  join editoriales as e
  on l.codigoeditorial=e.codigo;
 
Al presentar los campos, en este caso, no es necesario aclarar a qué tabla pertenecen porque los campos solicitados no se repiten en ambas tablas, pero si solicitáramos el código del libro, debemos especificar de qué tabla porque el campo "codigo" se repite en ambas tablas ("libros" y "editoriales"):

 select l.codigo,titulo,autor,nombre from libros as l
  join editoriales as e
  on l.codigoeditorial=e.codigo;
 
Si obviamos la referencia a la tabla, la sentencia no se ejecuta y aparece un mensaje indicando que el campo "codigo" es ambiguo.



PROBLEMA RESUELTO



Trabajamos con las tablas "libros" y editoriales" de una librería.
Elimine las tablas, si existen:

 drop table if exists libros, editoriales;
 
Cree las siguientes tablas:

 create table libros(
  codigo int unsigned auto_increment,
  titulo varchar(40) not null,
  autor varchar(30) not null default 'Desconocido',
  codigoeditorial tinyint unsigned not null,
  precio decimal(5,2) unsigned,
  cantidad smallint unsigned default 0,
  primary key (codigo)
 );
 
 create table editoriales(
  codigo tinyint unsigned auto_increment,
  nombre varchar(20) not null,
  primary key(codigo)
 );
 
Cargamos algunos registros en la tabla "editoriales":

 insert into editoriales (nombre) values('Paidos');
 insert into editoriales (nombre) values('Emece');
 insert into editoriales (nombre) values('Planeta'); 
 insert into editoriales (nombre) values('Sudamericana');
 
Cargamos algunos registros en la tabla "libros":

 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)
  values('El Aleph','Borges',3,43.5,200);
 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)
  values('Alicia en el pais de las maravillas','Lewis Carroll',2,33.5,100);
 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)
  values('Aprenda PHP','Mario Perez',1,55.8,50);
 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)
  values('Java en 10 minutos','Juan Lopez',1,88,150);
 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)
  values('Alicia a traves del espejo','Lewis Carroll',1,15.5,80);
 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)
  values('Cervantes y el quijote','Borges- Bioy Casares',3,25.5,300);
 
Mostramos todos los datos de la tabla "libros":

 select * from libros;
 
Queremos también el nombre de la editorial, necesitamos ambas tablas:

 select * from libros
  join editoriales
  on libros.codigoeditorial=editoriales.codigo;
 
Analicemos la consulta: indicamos el nombre de la tabla luego del "from" ("libros"), unimos esa 
tabla con "join" y el nombre de la otra tabla ("editoriales"), luego especificamos la condición 
para enlazarlas con "on", es decir, el campo por el cual haremos coincidir los registros de las 
dos tablas, el campo "codigoeditorial" de "libros" y el campo "codigo" de "editoriales" son los 
que enlazarán ambas tablas.

Si no especificamos por qué campo relacionamos ambas tablas, es decir, omitimos la condición "on":

 select * from libros
  join editoriales;
 
el resultado es el producto cartesiano de ambas tablas. Un "join" sin condición "on" para la unión 
genera un resultado en el que aparecen todas las combinaciones de los registros de ambas tablas. 

Veamos lo que sucede si omitimos la referencia a las tablas al nombrar el campo "codigo" 
(nombre de campo que contienen ambas tablas):

 select * from libros
  join editoriales
  on codigoeditorial=codigo;
 
aparece un mensaje de error indicando que "codigo" es ambiguo.

Por ello, si hacemos referencia a un campo de una tabla que tiene el mismo nombre que el campo 
de la otra tabla consultada, debemos especificar a cuál tabla pertenece dicho campo.

Para simplificar la sentencia podemos usar un alias para cada tabla:

 select * from libros as l
  join editoriales as e
  on l.codigoeditorial=e.codigo;
 
En la consulta anterior, el código de la editorial aparece 2 veces, desde la tabla "libros" y 
"editoriales". Podemos solicitar que nos muestre algunos campos:

 select titulo,autor,nombre from libros as l
  join editoriales as e
  on l.codigoeditorial=e.codigo;
 
Si queremos ver el código del libro, al presentar el campo "codigo", debemos especificar 
de qué tabla, porque el campo "codigo" se repite en ambas tablas ("libros" y "editoriales"):

 select l.codigo,titulo,autor,nombre from libros as l
  join editoriales as e
  on l.codigoeditorial=e.codigo;
 
Si obviamos la referencia a la tabla, la sentencia no se ejecuta y aparece un mensaje indicando 
que el campo "codigo" es ambiguo:

 select codigo,titulo,autor,nombre from libros as l
  join editoriales as e
  on l.codigoeditorial=e.codigo;
 
 
 
PROBLEMA PROPUESTO 
 
 
Una empresa tiene registrados sus clientes en una tabla llamada "clientes", 
también tiene una tabla "provincias" donde registra los nombres de las provincias.

1- Elimine la tabla "clientes" y "provincias", si existen:
 
2- Créelas con las siguientes estructuras:
 
 create table clientes (
  codigo int unsigned auto_increment,
  nombre varchar(30) not null,
  domicilio varchar(30),
  ciudad varchar(20),
  codigoprovincia tinyint unsigned,
  telefono varchar(11),
  primary key(codigo)
 );

 create table provincias(
  codigo tinyint unsigned auto_increment,
  nombre varchar(20),
  primary key (codigo)
 );

3- Ingrese algunos registros para ambas tablas:
 
 insert into provincias (nombre) values('Cordoba');
 insert into provincias (nombre) values('Santa Fe');
 insert into provincias (nombre) values('Corrientes');
 insert into provincias (nombre) values('Misiones');
 insert into provincias (nombre) values('Salta');
 insert into provincias (nombre) values('Buenos Aires');
 insert into provincias (nombre) values('Neuquen');

 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Lopez Marcos', 'Colon 111', 'Córdoba',1,'null');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Perez Ana', 'San Martin 222', 'Cruz del Eje',1,'4578585');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Garcia Juan', 'Rivadavia 333', 'Villa Maria',1,'4578445');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Perez Luis', 'Sarmiento 444', 'Rosario',2,null);
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Pereyra Lucas', 'San Martin 555', 'Cruz del Eje',1,'4253685');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Gomez Ines', 'San Martin 666', 'Santa Fe',2,'0345252525');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Torres Fabiola', 'Alem 777', 'Villa del Rosario',1,'4554455');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Lopez Carlos', 'Irigoyen 888', 'Cruz del Eje',1,null);
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Ramos Betina', 'San Martin 999', 'Cordoba',1,'4223366');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Lopez Lucas', 'San Martin 1010', 'Posadas',4,'0457858745');

4- Obtenga los datos de ambas tablas, use alias:
 
 
5- Obtenga la misma información anterior pero ordenada por nombre 
del cliente:
 

6- Omita la referencia a las tablas en la condición "on" para verificar 
que la sentencia no se ejecuta porque el nombre del campo "codigo" es 
ambiguo (ambas tablas lo tienen):
 



Otros problemas:

A) Un club dicta clases de distintos deportes. En una tabla llamada 
"socios" guarda los datos de sus socios y en una tabla denominada 
"inscriptos" almacena la información necesaria para las 
inscripciones de los socios a los distintos deportes.

1- Elimine las tablas si existen.

2- Cree las tablas:
 
 create table socios(
  documento char(8) not null,
  nombre varchar(30),
  domicilio varchar(30),
  primary key(documento)
 );

 create table inscriptos(
  documento char(8) not null, 
  deporte varchar(15) not null,
  año year,
  matricula char(1), /*si esta paga ='s' sino 'n'*/
  primary key(documento,deporte,año)
 );

3- Ingrese algunos registros para ambas tablas:
 
 insert into socios values('22333444','Juan Perez','Colon 234');
 insert into socios values('23333444','Maria Lopez','Sarmiento 465');
 insert into socios values('24333444','Antonio Juarez','Caseros 980');

 insert into inscriptos values ('22333444','natacion','2005','s');
 insert into inscriptos values ('22333444','natacion','2006','n');
 insert into inscriptos values ('23333444','natacion','2005','s');
 insert into inscriptos values ('23333444','tenis','2006','s');
 insert into inscriptos values ('23333444','natacion','2006','s');
 insert into inscriptos values ('24333444','tenis','2006','n');
 insert into inscriptos values ('24333444','basquet','2006','n');

4- Muestre el nombre del socio y todos los campos de la tabla "inscriptos":
 

5- Omita la referencia a las tablas en la condición "on" para verificar que 
la sentencia no se ejecuta porque el nombre del campo "documento" es ambiguo 
(ambas tablas lo tienen):
 

6- Muestre el nombre de los socios y los deportes en los cuales están 
inscriptos este año:
 
7- Muestre el nombre y todas las inscripciones del socio con número de 
documento='23333444':
 

B) Una pequeña biblioteca de barrio registra los préstamos de sus libros 
en una tabla llamada "prestamos" y los datos de sus libros en una tabla 
llamada "libros".

1- Elimine las tablas, si existen.

2- Cree las tablas:
 
 create table libros(
  codigo int unsigned auto_increment,
  titulo varchar(40),
  autor varchar (30),
  editorial varchar (15),
  primary key (codigo)
 );

 create table prestamos(
  codigolibro int unsigned not null,
  documento char(8) not null,
  fechaprestamo date not null,
  fechadevolucion date,
  primary key(codigolibro,fechaprestamo)
 );

3- Ingrese algunos registros para ambas tablas:
 
 insert into libros values (15,'Manual de 1º grado','Moreno Luis','Emece');
 insert into libros values (28,'Manual de 2º grado','Moreno Luis','Emece');
 insert into libros values (30,'Alicia en el pais de las maravillas','Lewis Carroll','Planeta');
 insert into libros values (35,'El aleph','Borges','Emece');

 insert into prestamos
  values(15,'22333444','2006-07-10','2006-07-12');
 insert into prestamos
  values(15,'22333444','2006-07-20','2006-07-21');
 insert into prestamos (codigolibro,documento,fechaprestamo)
  values(15,'23333444','2006-07-25');
 insert into prestamos (codigolibro,documento,fechaprestamo)
  values(30,'23333444','2006-07-28');
 insert into prestamos (codigolibro,documento,fechaprestamo)
  values(28,'25333444','2006-08-10');

4- Muestre todos los datos de los préstamos, incluyendo el nombre del 
libro (join con "libros"):
 
5- Muestre la información de los préstamos del libro "Manual de 1º grado":
 
6- Muestre los títulos de los libros, la fecha de préstamo y el documento 
del socio de todos los libros que no han sido devueltos:
 

C) Una clínica registra las consultas de los pacientes en una tabla llamada 
"consultas" y en otra tabla denominada "obrassociales" almacena los datos de 
las obras sociales que atiende.

1- Elimine las tablas si existen.

2- Cree las tablas: 
 
 create table consultas(
  fecha date,
  hora time,
  documento char(8) not null,
  codigoobrasocial tinyint unsigned,
  medico varchar(30),
  primary key(fecha,hora,medico)
 );

 create table obrassociales(
  codigo tinyint unsigned auto_increment,
  nombre varchar(15),
  monto decimal(5,2) unsigned,
  primary key(codigo)
 );

3- Ingrese algunos registros:
 
 insert into obrassociales (nombre,monto)
  values('PAMI',2);
 insert into obrassociales (nombre,monto)
  values('IPAM',5);
 insert into obrassociales (nombre,monto)
  values('OSDOP',3);

 insert into consultas values('2006-08-10','8:00','22333444',1,'Perez');
 insert into consultas values('2006-08-10','10:00','22333444',1,'Lopez');
 insert into consultas values('2006-08-10','8:30','23333444',1,'Perez');
 insert into consultas values('2006-08-10','9:00','24333444',2,'Perez');
 insert into consultas values('2006-08-10','10:00','25333444',3,'Perez');
 insert into consultas values('2006-08-10','8:30','25333444',1,'Garcia');
 insert into consultas values('2006-09-10','8:30','25333444',1,'Lopez');

4- Muestre la fecha,hora,documento del paciente, médico, nombre y monto de 
la obra social de todas las consultas (join con "obrassociales"):
 
5- Muestre fecha,hora,documento del paciente y nombre de la obra social para 
las consultas del doctor "Perez":
 
6- Muestre las obras sociales DISTINTAS que atendió el doctor "Perez" el día 
"2006-08-10":