[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

how to fetch records having no match with there ids.

i have 2 tables

Member             and         Member_Paid
----                  ------------
1-                                                
2-                  2-      
3-      

i have 2 tables Member and Member_paid

if i need a record

1-All paid members
2-All Un paid members


for 1st it sill work like this

select * from member and member_paid where member.mid is equal to member_paid.mid

if will get those records that matched like
results
2

but what will be SINGLE QUERY for unpaid members
results
1
3

no multiple queries or query within the query i need it with single query having some join

or other alternative way plz guys help me out.
0
naeembhatti
Asked:
naeembhatti
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT *
FROM Member
WHERE MemberID NOT IN (SELECT MemberId from Member_Paid )
0
 
naeembhattiAuthor Commented:
oh aneeshattingal this is again multiple queries, i can't use them. is that possible with some joins?
0
 
Aleksandar BradarićSoftware DeveloperCommented:
With no subselects:
---
SELECT
  M.*
FROM
  Member M
  LEFT JOIN Member_Paid MP ON MP.MemberId = M.MemberId
WHERE
  MP.MemberId IS NULL
---
0
 
hernst42Commented:
Without a subselect, just use a outer join:
select * from member left outer join member_paid on (member.mid = member_paid.mid) WHERE member_paid.mid IS NULL

0
 
naeembhattiAuthor Commented:
waho it all works

thanks
aneeshattingal
leannonn
hernst42
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now