I have TableA and TableB and a relation TableAB for a many to many (N-M) relation
TableA JOIN TableAB JOIN TableB
How can I add an AND-condition to check if TableAB has entries for multiple conditions in TableB
Maybe a stupid but simple example:
TableA = firstnames
TableB = lastnames
I want all firstnames that are used in combination with lastname "Smith", "Black" AND "Taylor"
I thought about using
WHERE lastname = "Smith" or lastname = "Black" or lastname = "Taylor"
GROUP BY firstname and check if count(*) = 3
But this seems to be not the right way, as lastname = "Smith" or lastname = "Smith" or lastname = "Smith" should not return an empty table.
And it should be easy to extend, as the filter is user input, and don't know how many conditions I get.
Bonus question: I use Sequelize with a Many-to-Many Association, not sure how to put this in here
EDIT:
Example: TableA
| id | firstname |
|---|---|
| 1 | Tom |
| 2 | Alice |
| 3 | Bob |
TableB
| id | lastname |
|---|---|
| 1 | Smith |
| 2 | Black |
| 3 | Taylor |
TableAB
| id | id_firstname | id_lastname |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 2 | 2 |
| 2 | 3 | 3 |
Meaning we have Tom Smith, Alice Smith, Alice Black, Bob Taylor
Query: Give me all firstnames with lastname Smith AND Black
Result: Alice
Thank you for your reply. The second one is similar to what I thought would work:
select A.Firstname, B.Lastname
from TableA A
inner join TableAB AB on A.ID = AB.IDA
inner join TableB B on B.ID = AB.IDB AND Lastname in ('Smith', 'Black')
group by A.Firstname having count(*) = 2
But it must be checked if all user inputs of lastnames are distinct as "in ('Smith', 'Smith')" will not work with count = 2
That's why I thought there exists better solutions. The first suggestion would be fine with this, but its a mess to implement this automated with prepared statements in sequelize.