MySQL将两个SELECT查询组合成一个检查重复值

Question

Given these two simple queries:

SELECT materialID FROM materials WHERE compoundNumber = "<some user input1>";
SELECT materialID FROM materials WHERE vendorCompoundNumber = "<some user input2>";

How can I make them into one query with the result set look something like this:

materialID | compoundDupFound | vendorCompoundDupFound

1          |                0 |                      1

              - - or - -

2          |                1 |                      0 

              - - or - -

3          |                1 |                      1

I realize I can perform one then the other but I'm wondering if there is a clever built in way to construct a query to do both at once.

The columns compoundNumber & vendorCompoundNumber need to be unique in the database so I'm checking a form and making sure the user inputs something unique. I will display an error message based on the result set.

Answer 1

Can use the OR operator to combine queries

SELECT materialID, compoundNumber AS compoundDupFound, vendorCompoundNumber AS vendorCompoundDupFound
FROM materials 
WHERE compoundNumber = "input1"
   OR vendorCompoundNumber = "input2";

Based on the comments, it looks like you need to know which was matched as well. So this points back to a UNION statement, but with a small change from the other answer talking about this path

SELECT materialID, compoundNumber AS compoundDupFound, NULL as vendorCompoundDupFound
    FROM materials 
    WHERE compoundNumber = "input1"
UNION
SELECT materialID, NULL, vendorCompoundNumber AS vendorCompoundDupFound
    FROM materials 
    WHERE vendorCompoundNumber = "input2"

That should give you output as close as possible to the original question. This way you will get matched records or NULL, and then can tell which was matched.