CODE
CREATE TABLE pbm_album (
id TINYINT(8) AUTO_INCREMENT,
parent_id TINYINT(8)
pbm_name VARCHAR(100),
pbm_desc VARCHAR(150),
pbm_order TINYINT(8),
pbm_image varchar(100),
pbm_cat_id TINYINT(8),
pbm_create_date int NOT NULL,
FOREIGN KEY pbm_cat_id REFERENCES pbm_catalog(id),
PRIMARY KEY(id)
)
CREATE TABLE pbm_photo (
id TINYINT(8) AUTO_INCREMENT,
pbm_name varchar(20) NOT NULL,
pbm_title varchar(60) NULL,
pbm_album_id TINYINT(8) NOT NULL,
pbm_create_date int NOT NULL,
FOREIGN KEY pbm_album_id REFERENCES pbm_album(id),
PRIMARY KEY(id)
)
id TINYINT(8) AUTO_INCREMENT,
parent_id TINYINT(8)
pbm_name VARCHAR(100),
pbm_desc VARCHAR(150),
pbm_order TINYINT(8),
pbm_image varchar(100),
pbm_cat_id TINYINT(8),
pbm_create_date int NOT NULL,
FOREIGN KEY pbm_cat_id REFERENCES pbm_catalog(id),
PRIMARY KEY(id)
)
CREATE TABLE pbm_photo (
id TINYINT(8) AUTO_INCREMENT,
pbm_name varchar(20) NOT NULL,
pbm_title varchar(60) NULL,
pbm_album_id TINYINT(8) NOT NULL,
pbm_create_date int NOT NULL,
FOREIGN KEY pbm_album_id REFERENCES pbm_album(id),
PRIMARY KEY(id)
)
hi I got a issue when selecting some data.
What I'am doing is to select all albums and photos that presents in the current album. However, for now I just can select all albums under the current album.
This is the statement I do obtain albums
CODE
SELECT a.id, a.pbm_name, a.pbm_desc, a.create_date, count(p.pbm_album_id)
FROM pbm_album a
LEFT JOIN pbm_photo p ON
a.pbm_parent_id = p.pbm_album_id AND p.pbm_album=[album_id]
GROUP BY a.pbm_name
ORDER BY a.create_date
FROM pbm_album a
LEFT JOIN pbm_photo p ON
a.pbm_parent_id = p.pbm_album_id AND p.pbm_album=[album_id]
GROUP BY a.pbm_name
ORDER BY a.create_date
It provides me all info about album, but when a album that both hold albums and photos.
How do I select both of it as the tables structure are different between the two.
[ref]
[ Album Name ] - [ Some Photos ] [ Album Name ] - [ Album Name ]
Thanks
Eric,
