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.
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.
Can also use UNION. If you do not want to check for duplicates - you need to use UNION ALL.
SELECT materialID FROM materials WHERE compoundNumber = "<some user input1>"
UNION
SELECT materialID FROM materials WHERE vendorCompoundNumber = "<some user input2>"