Sql Selection Issues

[magiccode9 0]

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))

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

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

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,

[derickkoo 0]

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))

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

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

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,

Hey, does a column "pbm_album" in the table "pbm_photo" ???