MySQL: Combining multiple where conditions

Question

I'm working on a menu system that takes a url and then queries the db to build the menu.
My menu table is:

+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment | 
| node_id | int(11)      | YES  |     | NULL    |                | 
| parent  | int(11)      | YES  |     | NULL    |                | 
| weight  | int(11)      | YES  |     | NULL    |                | 
| title   | varchar(250) | YES  |     | NULL    |                | 
| alias   | varchar(250) | YES  |     | NULL    |                | 
| exclude | int(11)      | YES  |     | NULL    |                | 
+---------+--------------+------+-----+---------+----------------+

The relevant columns for my question are alias, parent and node_id.
So for a url like: http://example.com/folder1/folder2/filename
Alias would potentially = "filename", "folder1", "folder2"
Parent = the node_id of the parent folder.

What I know is how to split the url up into an array and check the alias for a match to each part. What I don't know is how to have it then filter by parent whose alias matches "folder2" and whose parent alias matches "folder1". I'm imagining a query like so:

select * from menu 
where alias='filename' and 
where parent = node_id 
where alias='folder2' and parent = node_id 
where alias='folder1'

Except I know that the above is wrong. I'm hoping this can be done in a single query.

Thanks for any help in advance!

Answer 1

I can't get what query you want but here is 2 rules for you:

• WHERE statement should be only one.
• resulting expression will be applied to the each row, one-by-one. Not to the "whole" table, as you probably think. So, you have to think hard when creating expression. there would be no match with alias='folder2' and alias='folder1' at the same time

Answer 2

This should do it for you. It links all the nodes through parent (if there is one), and will retrieve info for all of the levels in one record.

select * 
from menu m1
left outer join menu m2 on m1.parent = m2.node_id
left outer join menu m3 on m2.parent = m3.node_id
where m1.alias = 'filename' 
    and m2.alias = 'folder2'
    and m3.alias = 'folder1'

Answer 3

select * from menu 
where alias='filename' and 
parent = (select node_id from menu
          where alias='folder2' and
          parent = (select node_id from menu
                    where alias='folder1'
                   )
         )