SQL：选择与另一个表中的内容匹配的所有行

Question

I am trying to write a query like this (hopefully with only one Query): Select all messages from inbox table where the "SenderNumber" of the message is found in "Sentitems" table and the CreatorID is 'Martin'

For example my inbox table looks like this:

| SenderNumber   |   TextMessage    |
    11111111       Yes, nice world!
    22222222         Howdy folks!

And my sentitems table looks very similar

| DestinationNumber  | TextMessage |  CreatorID
      11111111         Hello world?    Martin
      22222222          How you do?     John

I would like to get all message from Inbox table with this condition - there is an entry for this "SenderNumber" / "DestinationNumber" in my sentitems table and the CreatorID is 'Martin'.

So in this case it would return this entry because the creatorID for the other number is not 'Martin' but 'John'

| SenderNumber   |   TextMessage    |
    11111111       Yes, nice world!

Answer 1

Try this one :

SELECT i.SenderNumber, i.TextMessage
FROM inbox as i
INNER JOIN sentitems as s ON s.DestinationNumber = i.SenderNumber AND s.CreatorID = 'Martin'

The logic is :

1. You select what you want, here SenderNumber and TextMessage from the table inbox
2. Now you use an INNER JOIN with the table sentitems and two conditions : DestinationNumber from table sentitems and SenderNumber from table inbox are the same + CreatorID with the value you want, here 'Martin'

Using INNER JOIN will return only the result that respect the two conditions. For more information, take a look to the documentation : https://sql.sh/cours/jointures/inner-join