SQL查询：我需要两个查询，还是可以使用嵌套子查询

Question

This question may have been asked before but I don't really know what verbiage to search with.

I have a mysql DB that has a table with 3 columns [ID, fieldName and fieldValue] that is used to describe attributes of objects in another table. The ID field stores the foreign key of object in the other table and the fieldName and fieldValue store things like title, description, file size and summary.

I am trying to write a query that returns rows where a fieldName and fieldValue pair match known values and the returned row ID has a another distinct fieldValue in another row. Right now I am accomplishing it with two queries and an if statement. Here is the sudo code:

$result = SELECT * FROM table_a WHERE fieldName = 'title' and fieldValue = 'someTitle'
$test = SELECT * FROM table_a WHERE fieldValue = 'someValue' and id = '{$result['id']}'
if ($test) {
  /* Result Found */
}

Answer 1

You can self-join the table:

SELECT * FROM table_a AS s1 
JOIN table_a AS s2 USING (id)
WHERE
    s1.fieldName = 'Title' AND s1.fieldValue = 'someTitle'
    AND s2.fieldValue = 'someValue'