MySQL: i need to pull the same column name from multiple rows into single query based on same conditions

I want to combine the following queries into a single query:

SELECT val FROM resource WHERE facet="all" and urlId="1234"
SELECT val FROM resource WHERE facet="your" and urlId="1234"
SELECT val FROM resource WHERE facet="base" and urlId="1234"

Oh -- there are multiple "facets" for each urlId. But only one row per "facet". This table was setup by somebody else. We are migrating away. Obviously it would be better to have a column for each "facet" and a single row, but it's too late for that.

I can do this with multiple queries, but would like to have a faster/better solution.

<pre>
mysql> describe resource;
+-------------+------------开发者_开发知识库-------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                                | Null | Key | Default           | Extra                       |
+-------------+-------------------------------------+------+-----+-------------------+-----------------------------+
| resId       | int(11) unsigned                    | NO   | PRI | NULL              | auto_increment              |
| urlId       | int(11)                             | NO   | MUL | NULL              |                             |
| sectionId   | int(3)                              | YES  |     | NULL              |                             |
| mode        | enum('archive','live','edit','dev') | NO   |     | edit              |                             |
| facet       | varchar(50)                         | NO   | MUL | NULL              |                             |
| typeId      | int(1)                              | NO   |     | 1                 |                             |
| val         | mediumtext                          | NO   |     | NULL              |                             |
| itemOrder   | tinyint(2)                          | NO   |     | 1                 |                             |
| editorId    | varchar(30)                         | NO   |     | NULL              |                             |
| created     | datetime                            | NO   |     | NULL              |                             |
| lastUpdated | timestamp                           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-------------------------------------+------+-----+-------------------+-----------------------------+
11 rows in set (0.05 sec)
</pre>

Remember: in soviet russia, SQL queries you.

This is my preferred method which will use your indexes:

SELECT resource.val FROM
  ( (SELECT "all" AS name) UNION (SELECT "your") UNION (SELECT "base") ) AS facets
  LEFT JOIN resource ON( resource.facet  = facets.name AND resource.urlId = "1234" )

You can use the mysql IN() comparison operator.

E.g:

SELECT `val` FROM `resource` WHERE `urlId` = "1234" AND `facet` IN("all", "your", "base");

Result in three columns:

SELECT (SELECT val FROM resource WHERE facet="all" and urlId="1234"),
       (SELECT val FROM resource WHERE facet="your" and urlId="1234"),
       (SELECT val FROM resource WHERE facet="base" and urlId="1234")

But I personally don't recommend this, it's not scalable. Go with my other answer.

SELECT val
FROM resource
WHERE urlId = '1234'
AND facet IN (
  SELECT "all", "your", "base";
)

SELECT val
FROM resource
WHERE urlId = '1234'
AND facet IN ('all', 'your', 'base');

SELECT val
FROM resource 
WHERE (facet = "all" or facet = "your" or facet = "base")
AND urlId = "1234"

or

SELECT val
FROM resource 
WHERE facet in ("all", "your", "base")
AND urlId = "1234"