When you use SELECT as part of a condition, the subclause can only return a single item because that is how comparison operators like "=" work: if you compare one item with two others, the result is not defined, just as it isn't in the real world. Is this orange equal to this (one apple, two bananas, one orange) or not? You don' know, I don't know - simply because there is no "real" answer.
Probably, you need to do a JOIN instead, but since we have no idea of the structure of your tables and the relationships between them, we can't say for sure, or recommend any code you could try. So think about the data in your tables, and exactly what you are trying to retrieve before you start trying to code a complex query! Or give us sample input and output and explain why it's like that ...
But you have a much more serious problem!
Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.
When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
DROP TABLE MyTable;
A perfectly valid "delete the table" command
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.
So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
Submit an article or tip