i'm facing a problem with sql queries... is there a shorthand for AND condition like IN= many OR conditions?? when not, any idea about this situation. considering we have 2 tables student and topics with many to many relation.
Problem: get all student who choose topic a And b And c. Note: the list of topics in not fixe. Table: student
| student | studentID |
|---|---|
| w | 1 |
| x | 2 |
| y | 3 |
| z | 4 |
Table: Topic
| topic | topicID |
|---|---|
| a | 1 |
| b | 2 |
| c | 3 |
| e | 4 |
| f | 5 |
| g | 6 |
| h | 7 |
| i | 8 |
Table: StudentTopic
| studentID | TopicID |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 3 |
| 1 | 6 |
That is what i tried: **select *** **from **student **where ** topic in (a,b,c). the result is not correct because know that IN is the shorthand for many OR conditions.
CodePudding user response：
There's no shorthand in SQL for this.
Generally, it's called relational-division. This is to fit it into the algebra metaphor to form a counter to the JOIN operation, which could be the analog of multiplication in arithmetic.
Because relational division is less commonly used than join, the SQL language does not provide a very easy shorthand. You can get the result you want, but it's not as quick as using JOIN.
SELECT studentId
FROM StudentTopic
WHERE topicId IN (1,2,3)
GROUP BY studentId
HAVING COUNT(DISTINCT topicId) = 3;
There are other ways to get the same result, for example:
SELECT s1.studentId
FROM StudentTopic AS s1
INNER JOIN StudentTopic AS s2 USING (studentId)
INNER JOIN StudentTopic AS s3 USING (studentId)
WHERE s1.topicId = 1
AND s2.topicId = 2
AND s3.topicId = 3;
You can follow the relational-division tag to read about other solutions. But none of them are very simple. It's just not an operation that has been given special shorthand syntax in SQL.