MySQL: Query for two values' presence

Question

I have a table with fields id, text, and title. There may be multiple rows with the same title (title is not unique). Additionally, id is the primary key.

I need only to know if there are at least one row with title="A" that contains the string "aaa" in text, and additionally at least one row with title="B" that contains the string "bbb" in text.

This is what I tried:

mysql> SELECT (SELECT text FROM table WHERE title = 'A') as aText, (SELECT text FROM table WHERE title = 'B') as bText;

I had planned on parsing the values of aText and bText for the strings "aaa" and "bbb" respectfully with PHP. However, I have two issues:

1) Major issue: Due to title not being unique, the subqueries may return multiple rows. MySQL breaks on that happening with this error:

ERROR 1242 (21000): Subquery returns more than 1 row

2) Minor issue: The reason that I am parsing in PHP is to avoid using MySQL's LIKE operator. Would I be better off doing the parsing of the string right there in MySQL as such:

mysql> SELECT (SELECT text FROM table WHERE title = 'A' AND text LIKE '%aaa%') as aText, (SELECT text FROM table WHERE title = 'B' AND text LIKE '%bbb%') as bText;

@MichaelMior: Because I don't want to parse the entire table. I am not certain and can find no reference if LIKE is conditional. Therefore, I now ask [this](http://stackoverflow.com/questions/8790245/mysql-is-and-conditional-or-logical). Thanks. — dotancohen, Jan 09 '12 at 14:54

score 2 · Answer 1 · answered Jan 09 '12 at 14:18

2

SELECT (SELECT count(*) FROM table WHERE title='A') AS A_count,
    (SELECT count(*) FROM table WHERE title='B') AS B_count
WHERE (A_count > 0) AND (B_count > 0)

this would return 1 row (with the counts) and no rows if either'A' or 'B' is not present.

A subquery returning data to a field in a parent query can only ever return a single value. You're basically replacing a fieldname with a query result, which means the query result has to behave the same as a normal table field would. Hence the "returns more than 1 row" error - you're returning all of the rows where 'A' or 'B' matches, which is not a single field - it's a column.

answered Jan 09 '12 at 14:18

Marc B

356,200
43
426
500

Thanks, but that leaves me with no way to test the presence of the strings `text` field. That might work if it turns out that MySQL's `AND` operator is conditional, as [asked here](http://stackoverflow.com/questions/8790245/mysql-is-and-conditional-or-logical). – dotancohen Jan 09 '12 at 14:57
you can still do `WHERE title='A' OR text LIKE '%aaa%'` in the subqueries. – Marc B Jan 09 '12 at 15:16

sgcharlie · Answer 2 · 2012-01-09T15:16:11.650

2

If you just wanted to know if those rows existed, you could do something like this:

SELECT COUNT(1)
FROM table
WHERE (title = 'A' AND text = '%aaa%')
OR (title = 'B' AND text = '%bbb%')

-- EDIT --

Based on your comment, you may want to try the following query instead:

SELECT COUNT(1), title
FROM table
WHERE (title = 'A' AND text = '%aaa%')
OR (title = 'B' AND text = '%bbb%')
GROUP BY title

Check for two rows, both with a COUNT(1) of greater than zero.

edited Jan 09 '12 at 15:16

answered Jan 09 '12 at 14:22

sgcharlie

1,006
1
10
25

That would match if I had two rows for (title = 'A' AND text = '%aaa%') yet none for (title = 'B' AND text = '%bbb%'). – dotancohen Jan 09 '12 at 14:55

score 1 · Answer 3 · answered Jan 09 '12 at 14:22

1

You can self-join:

SELECT t1.text as `atext`, t2.text as `btext`
table as t1, table as t2
WHERE t1.title = 'A' AND t1.text LIKE '%aaa%' AND t2.title = 'B' AND t2.text LIKE '%bbb%';

If you want to do your parsing in php just leave off the where statement, but if what you're doing in php is equivalent to mysql's like statement I don't know why you wouldn't do it in the query.

answered Jan 09 '12 at 14:22

Ben D

14,321
3
45
59

In PHP I know that I'm only parsing the rows that matched title = 'A' and not the whole table! – dotancohen Jan 09 '12 at 14:58

dotancohen · Accepted Answer · 2012-02-03T15:08:23.673

It is probably not good form to answer one's own question, but for the sake of the fine archives at the expense of my karma:

The query that I employed is:

SELECT count(*) FROM (
    SELECT DISTINCT title FROM (
        SELECT title, text FROM table WHERE title IN ('A', 'B')
    )
    AS filtered
    WHERE (title='A' AND text LIKE '%aaa%')
       OR (title='B' AND text LIKE '%bbb%')
)
AS allResults;

The innermost query gets everything with a good title, the next query gets distinct titles for the real results. This setup ensures that the LIKE clause will not run on the entire table. I then wrap all that in a count, and if if equals 2 then I know that each condition was met.

Thank you everyone who contributed, I gained quite some insight from your answers!

MySQL: Query for two values' presence

4 Answers4