Mkdir Posted February 19, 2012 Report Share Posted February 19, 2012 (edited) No se si va aqui esto... pero quiero ver si me pueden ayudar...estoy practicando con mysql y cree una base de datos de ejemplo... pongo el codigo creado con mysqldump, con los datos y todo. [hide]-- MySQL dump 10.13 Distrib 5.5.20, for Linux (i686)---- Host: localhost Database: videoteca-- -------------------------------------------------------- Server version 5.5.20 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; ---- Table structure for table `actor`-- DROP TABLE IF EXISTS `actor`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `actor` ( `id_actor` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT, `nombre` varchar(50) DEFAULT NULL, `apellido` varchar(50) DEFAULT NULL, `fecha_nac` date DEFAULT NULL, PRIMARY KEY (`id_actor`)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */; ---- Dumping data for table `actor`-- LOCK TABLES `actor` WRITE;/*!40000 ALTER TABLE `actor` DISABLE KEYS */;INSERT INTO `actor` VALUES (0001,'sam','worthington','1976-10-02'),(0002,'leonardo','dicaprio','1974-09-14'),(0003,'kate','winslet','1975-10-05'),(0004,'mark','hamill','1951-09-25'),(0005,'harrison','ford','1942-06-13'),(0006,'sam','neil','1947-09-14'),(0007,'laura','dern','1967-02-10');/*!40000 ALTER TABLE `actor` ENABLE KEYS */;UNLOCK TABLES; ---- Table structure for table `actor_pelicula`-- DROP TABLE IF EXISTS `actor_pelicula`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `actor_pelicula` ( `actor` int(10) unsigned zerofill NOT NULL, `pelicula` int(10) unsigned zerofill NOT NULL, PRIMARY KEY (`actor`,`pelicula`), KEY `actor_pelicula_fk1` (`actor`), KEY `actor_pelicula_fk2` (`pelicula`), CONSTRAINT `actor_pelicula_ibfk_1` FOREIGN KEY (`actor`) REFERENCES `actor` (`id_actor`), CONSTRAINT `actor_pelicula_ibfk_2` FOREIGN KEY (`pelicula`) REFERENCES `pelicula` (`id_pelicula`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */; ---- Dumping data for table `actor_pelicula`-- LOCK TABLES `actor_pelicula` WRITE;/*!40000 ALTER TABLE `actor_pelicula` DISABLE KEYS */;INSERT INTO `actor_pelicula` VALUES (0000000001,0000000012),(0000000002,0000000010),(0000000003,0000000010),(0000000004,0000000013),(0000000005,0000000011),(0000000006,0000000011),(0000000007,0000000013);/*!40000 ALTER TABLE `actor_pelicula` ENABLE KEYS */;UNLOCK TABLES; ---- Table structure for table `director`-- DROP TABLE IF EXISTS `director`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `director` ( `nombre` varchar(50) DEFAULT NULL, `apellido` varchar(50) DEFAULT NULL, `fecha_nac` date DEFAULT NULL, `id_director` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id_director`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */; ---- Dumping data for table `director`-- LOCK TABLES `director` WRITE;/*!40000 ALTER TABLE `director` DISABLE KEYS */;INSERT INTO `director` VALUES ('james','cameron','1954-10-16',0001),('george','lucas','1944-05-14',0002),('steven','spielberg','1946-12-18',0003);/*!40000 ALTER TABLE `director` ENABLE KEYS */;UNLOCK TABLES; ---- Table structure for table `director_pelicula`-- DROP TABLE IF EXISTS `director_pelicula`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `director_pelicula` ( `director` int(4) unsigned zerofill NOT NULL, `pelicula` int(4) unsigned zerofill NOT NULL, PRIMARY KEY (`director`,`pelicula`), KEY `director_pelicula_fk1` (`director`), KEY `director_pelicula_fk2` (`pelicula`), CONSTRAINT `director_pelicula_ibfk_1` FOREIGN KEY (`pelicula`) REFERENCES `pelicula` (`id_pelicula`), CONSTRAINT `director_pelicula_ibfk_2` FOREIGN KEY (`director`) REFERENCES `director` (`id_director`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */; ---- Dumping data for table `director_pelicula`-- LOCK TABLES `director_pelicula` WRITE;/*!40000 ALTER TABLE `director_pelicula` DISABLE KEYS */;/*!40000 ALTER TABLE `director_pelicula` ENABLE KEYS */;UNLOCK TABLES; ---- Table structure for table `formato`-- DROP TABLE IF EXISTS `formato`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `formato` ( `id_formato` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT, `descripcion` set('vhs','dvd','blueray') DEFAULT NULL, PRIMARY KEY (`id_formato`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */; ---- Dumping data for table `formato`-- LOCK TABLES `formato` WRITE;/*!40000 ALTER TABLE `formato` DISABLE KEYS */;INSERT INTO `formato` VALUES (0001,'vhs'),(0002,'dvd'),(0003,'blueray');/*!40000 ALTER TABLE `formato` ENABLE KEYS */;UNLOCK TABLES; ---- Table structure for table `genero`-- DROP TABLE IF EXISTS `genero`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `genero` ( `id_genero` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT, `nombre_genero` varchar(20) DEFAULT NULL, `descripcion_gen` varchar(50) DEFAULT 'sin descripcion', PRIMARY KEY (`id_genero`)) ENGINE=InnoDB AUTO_INCREMENT=117 DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */; ---- Dumping data for table `genero`-- LOCK TABLES `genero` WRITE;/*!40000 ALTER TABLE `genero` DISABLE KEYS */;INSERT INTO `genero` VALUES (0103,'terror','peliculas de miedo'),(0104,'comedia','peliculas para reirse'),(0105,'romantica','peliculas de amor'),(0106,'suspenso','sin descripcion'),(0107,'thriller','sin descripcion'),(0108,'infantil','sin descripcion'),(0109,'belica','sin descripcion'),(0110,'drama','sin descripcion'),(0111,'musical','sin descripcion'),(0112,'crimen','sin descripcion'),(0113,'fantasia','sin descripcion'),(0114,'deporte','sin descripcion'),(0115,'suspenso','sin descripcion'),(0116,'animacion','sin descripcion');/*!40000 ALTER TABLE `genero` ENABLE KEYS */;UNLOCK TABLES; ---- Table structure for table `pelicula`-- DROP TABLE IF EXISTS `pelicula`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `pelicula` ( `nombre` varchar(100) DEFAULT NULL, `duracion` time DEFAULT NULL, `id_pelicula` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT, `genero` int(4) unsigned zerofill NOT NULL, `formato` int(4) unsigned zerofill NOT NULL, PRIMARY KEY (`id_pelicula`), KEY `fkidgenero` (`genero`), KEY `fkiformato` (`formato`), CONSTRAINT `fkidgenero` FOREIGN KEY (`genero`) REFERENCES `genero` (`id_genero`), CONSTRAINT `fkiformato` FOREIGN KEY (`formato`) REFERENCES `formato` (`id_formato`)) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */; ---- Dumping data for table `pelicula`-- LOCK TABLES `pelicula` WRITE;/*!40000 ALTER TABLE `pelicula` DISABLE KEYS */;INSERT INTO `pelicula` VALUES ('titanic','03:00:00',0010,0105,0002),('star war','02:40:34',0011,0113,0003),('avatar','02:35:00',0012,0113,0002),('jurassic park','02:10:23',0013,0106,0002);/*!40000 ALTER TABLE `pelicula` ENABLE KEYS */;UNLOCK TABLES;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2012-02-18 22:44:47[/hide] si quieren agregar crear la base de datos en mysql para que puedan entender mejor mi problema lo pueden hacer guardando el código en un archivo.sql y ejecutar #mysql -u root -p > archivo.sql bueno... ya creada la tabla con todos sus datos se habrán dado cuenta que es una videoteca, la cosa es que al hacer un join para obtener los actores por película me sale un error, esta es la query: select pelicula.titulo, concat(actor.nombre,' ',actor.apellido) from actor,peliculajoin actor_pelicula on pelicula.id_pelicula = actor_pelicula.peliculajoin actor on actor_pelicula.actor = actor.id_actor; y este es el error: 0 22:52:02 select pelicula.titulo, concat(actor.nombre,' ',actor.apellido) from actor,pelicula join actor_pelicula on pelicula.id_pelicula = actor_pelicula.pelicula join actor on actor_pelicula.actor = actor.id_actor LIMIT 0, 1000 Error Code: 1066. Not unique table/alias: 'actor' estoy buscando la causa, pero si alguien tiene mas experiencia, su ayuda seria valiosa,... saludos y gracias. ***********************************************************edit********************************************************************* solucionado... el error era que cuando se hace un join solo se puede poner una tabla como origen de datos. saludos Edited February 19, 2012 by Mkdir Link to comment Share on other sites More sharing options...
AshWilliams Posted February 19, 2012 Report Share Posted February 19, 2012 Autosolucionado.....son errores que en su momento todos hemos pasado...se procede al cierre. Saludos :krider: Link to comment Share on other sites More sharing options...
Recommended Posts