XpeC04wv

1CREATE TABLE IF NOT EXISTS Albunes (
2    Id INT UNSIGNED NOT NULL AUTO_INCREMENT,
3    Nombre VARCHAR(100),
4    Descripcion TEXT NULL,
5    Fecha DATE NULL,
6    Publicar TINYINT(1) DEFAULT 1,
7    Propio TINYINT(1) DEFAULT 1,
8    Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
9    Updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
10    Activo TINYINT(1) DEFAULT 1,
11    PRIMARY KEY (Id)
12) ENGINE = InnoDB;
13
14
15CREATE TABLE IF NOT EXISTS Imagenes (
16    Id INT UNSIGNED NOT NULL AUTO_INCREMENT,
17    fk_Id_Albunes INT UNSIGNED NULL,
18    Codigo VARCHAR(150) NULL,
19    Path VARCHAR(255) NULL,
20    Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
21    Updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
22    Activo TINYINT(1) DEFAULT 1,
23    PRIMARY KEY (Id),
24    INDEX idx_Id_Albunes (fk_Id_Albunes ASC),
25    CONSTRAINT `fk_Imagenes_Albunes`
26        FOREIGN KEY (`fk_Id_Albunes`)
27        REFERENCES `Albunes`(`Id`)
28        ON DELETE SET NULL
29        ON UPDATE CASCADE
30) ENGINE = InnoDB;
31	
32-- suponiendo que consultamos para el albun Id=1
33SELECT * FROM 
34    Albunes AS a, 
35    (
36        SELECT COUNT(i.Id) AS Total FROM Imagenes AS i 
37        WHERE i.Activo=1 AND fk_Id_Albunes= 1
38    ) AS r
39WHERE a.Activo=1
40	
41SELECT * FROM 
42    Albunes AS a, 
43    (
44        SELECT COUNT(i.Id) AS Total FROM Imagenes AS i 
45        WHERE i.Activo=1 AND fk_Id_Albunes= a.Id
46    ) AS r
47WHERE a.Activo=1