CODEWITHSUNDEEP
sqloracle-databasejoin
user2967948
user2967948

Reputation: 549

SQL Query Joining 2 Tables and Getting Required Data

I have 2 Tables: Merchant having 3 columns MerchantID, MemberID, MerchantName Member having 3 columns MemberID, ReportID, MemberName

The sample values are:

MerchantID MemberID MerchantName
1101       101      ABC
1102       102      DEF
1103       103      XYZ

MemberID   ReportID MemberName
101        112      GHI
101        111      JKL
101        115      MNO
102        111      kjh
102        116      hgf
102        117      oiu
103        118      hgh
103        119      jhf

I need to get the MerchantNames which have Member IDs that are not associated with 111 Report ID. 

The query o/p should be: XYZ.

Kindly let me know the most optimized SQL query which can achieve this. Thanks in advance.

Upvotes: 0

Views: 57

Answers (2)

Sohail
Sohail

Reputation: 574

with JOIN: 

SELECT mer.MerchantName FROM [Merchant] mer
 LEFT JOIN [Member] mem ON mer.MemberId = mem.MemberId
  AND mem.ReportId != 111;

Or 

SELECT mer.MerchantName FROM [Merchant] mer
 LEFT JOIN [Member] mem ON mer.MemberId = mem.MemberId
  AND mem.ReportId <> 111;

Upvotes: 1

Mureinik
Mureinik

Reputation: 310993

This is a classic usecase for the EXISTS operator:

SELECT MerchantName
FROM   Merchant
WHERE  NOT EXISTS (SELECT 1
                   FROM   Member
                   WHERE  Member.MemberId = Merchant.MemberId AND 
                          ReportId = 111)

Upvotes: 1

Related Questions