CODEWITHSUNDEEP
mysql
Karl
Karl

Reputation: 147

MySQL: Combining multiple where conditions

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!

Upvotes: 0

Views: 330

Answers (3)

Nabb
Nabb

Reputation: 3494

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

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171421

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'

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157893

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

Upvotes: 0

Related Questions