Help with constructing a query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Crashin
Forum Contributor
Posts: 223
Joined: Mon May 06, 2002 3:42 pm
Location: Colorado

Help with constructing a query

Post by Crashin »

I have two tables that I'm working with in this scenario: album and album_rel. An album can have multiple "sub-albums' (or children) and can be a "sub-album" of multiple "parent" albums. The album table contains all individual-specific album information, and the album_rel table relates the albums together via two columns, child_id and parent_id, which hold the appropriate album "id" from the album table.

I'm trying to construct a query that will populate a drop-down with all available albums that are not already a child album of a given album being modified.

My query so far is as follows, and yes, I know it's not correct. I've put it here to try to better exemplify what I need:

Code: Select all

$query = "SELECT a.id, a.name FROM album AS a, album_rel AS r WHERE a.id != r.child_id AND r.parent_id != '" . $rowї'id'] . "' ORDER BY a.name";
How can I do this? :?
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post by Rob the R »

This would be easy to do with sub-queries, but sub-queries are not supported by MySQL. I am assuming that's what you are using.

I'm not sure if this would work, but you could try something like:

Code: Select all

$query = "SELECT DISTINCT a.id, a.name
FROM album AS a
LEFT JOIN album_rel AS r ON a.id = r.child_id
   AND " . $rowї'id'] . " = r.parent_id
WHERE r.child_id IS NULL
ORDER BY a.name";
You'll definitely have to use a LEFT JOIN, since you want records that are not in some other list or table. But I may not have it exactly right, so let me know if this helps.
User avatar
Crashin
Forum Contributor
Posts: 223
Joined: Mon May 06, 2002 3:42 pm
Location: Colorado

Post by Crashin »

Thanks so much for the tip! With some minor modifications it works...I was even able to exclude the selected album from the select list. The final query is shown below:

Code: Select all

"SELECT DISTINCT a.id, a.name 
	FROM album AS a 
	LEFT JOIN album_rel AS r ON a.id = r.child_id 
	AND r.parent_id = '" . $rowї'id'] . "'
	WHERE r.child_id IS NULL  
	AND a.id != '" . $rowї'id'] . "' 
	ORDER BY a.name";
Nice! 8)
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post by Rob the R »

Good catch! I hadn't even thought of excluding the original parent album. I'm glad it helped.
Post Reply