SELECT query with results from other SELECT query in WHERE clause-CodePudding

I have the following SQL query:

SELECT foo, bar FROM my_table WHERE foo = "aaa" OR bar = "yyy" LIMIT 1;

Let's assume the result of this query is:

foo	bar
xxx	yyy

Since I used LIMIT 1 there will be only one returning row.

Now I want to make a new SQL query with the results in WHERE clause, like this:

SELECT foo, bar FROM my_table WHERE foo = "xxx" OR bar = "yyy" LIMIT 1;

foo = "xxx" AND bar = "yyy" are the results of the first query.

Of course, I could grab the results from the first query and copy/paste them into the WHERE clause of the second query (manually or programmatically e.g. with PHP) but this isn't really convenient.

How can I combine these two queries into one?

CodePudding user response：

You can always use IN clause with two parameters

SELECT 
    foo, bar
FROM
    my_table
WHERE
    (foo , bar) IN (SELECT 
            foo, bar
        FROM
            my_table
        WHERE
            foo = 'aaa' OR bar = 'yyy'
        LIMIT 1)
LIMIT 1

CodePudding user response：

You can use the first query as a subquery on an inner join clause.

Try:

SELECT foo, 
       bar 
FROM my_table  mt1 
INNER JOIN ( SELECT foo, 
                    bar 
             FROM my_table 
             WHERE foo = "aaa" 
             OR bar = "yyy" 
             LIMIT 1
            ) as mt2 on mt1.foo=mt2.foo or mt1.bar=mt2.bar 
LIMIT 1 ;

Note. The query is not tested, try and let me know

CodePudding user response：

If you want to support prepared statements with an arbitrary number of comparisons, you could use a comma-delimited argument to FIND_IN_SET:

SELECT foo, bar
FROM my_table
WHERE FIND_IN_SET(foo, 'aaa,xxx') > 0     --prepared arg 1
    OR bar = 'yyy'                        --prepared arg 2
LIMIT 1;

This would allow you to send the arguments as: 'aaa,xxx', 'yyy', just 2 strings.