CODEWITHSUNDEEP
sqlselect
Katlock
Katlock

Reputation: 1398

SQL find all rows in one table that also exist in another table

I have the following tables:

  1. UserConversation: userId and ConversationId
  2. ConversationMessageReceipt: userId, conversationId, conversationMessageId and other columns
  3. ConversationMessageReceipt: conversationId, conversationMessageId, userid

I could add more details but I basically want to find rows in one table that also exists in another table, that is all the rows found from first table have to exists in another table.

Is there a way to find all the rows in the second table or get empty results?

Thanks

Upvotes: 0

Views: 56

Answers (3)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

If you wanted to check the table1 data in table 2, you can also use EXISTS keyword..

 SELECT *
 FROM UserConversation uc
 WHERE EXISTS (SELECT 1
    FROM ConversationMessageReceipt cm
    WHERE uc.userId=cm.userid
       AND uc.conversationid=cm.conversationid)

Upvotes: 2

Kushan
Kushan

Reputation: 10703

Use INNER JOIN your scenario, 

SELECT * FROM UserConversation INNER JOIN ConvesationMessageReceipt
ON  UserConversation.userId = ConvesationMessageReceipt.userId
AND UserConversation.conversationId = ConvesationMessageReceipt.conversationId

OR You want to get all records from first table Use LEFT JOIN Or LEFT OUTER JOIN

SELECT * FROM UserConversation LEFT JOIN ConvesationMessageReceipt
ON  UserConversation.userId = ConvesationMessageReceipt.userId
AND UserConversation.conversationId = ConvesationMessageReceipt.conversationId

Upvotes: 1

SMA
SMA

Reputation: 37083

Try joining them like below:

SELECT *
FROM UserConversation uc INNER JOIN ConvesationMessageReceipt cmr
ON  uc.userId = cmr.userId
AND uc.conversationId = cmr.conversationId

Upvotes: 1

Related Questions