Page 1 of 1

Help with constructing a query

Posted: Tue Nov 26, 2002 3:20 pm
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? :?

Posted: Tue Nov 26, 2002 4:08 pm
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.

Posted: Tue Nov 26, 2002 4:39 pm
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)

Posted: Wed Nov 27, 2002 7:49 am
by Rob the R
Good catch! I hadn't even thought of excluding the original parent album. I'm glad it helped.