You are here:

MS SQL/Select specific records

Advertisement


Question
Hello,

I have troubles building an sql query and I could use some help:

I have 2 tables: tblMembers, tblTransactions

In tblMembers I have the following records:

MemID     lname      fname
1      aaa        1111
2      bbb        2222

In tblTransactions I have the following records:

TransID     MemID      Code        fee
10          1          WEL-100     5
11          1          WEL-110     7
12          2          WEL-110     5

I want to make a query that will display all members and EXCLUDE those who have code WEL-100. In the table tblTransactions, the entires that should be excluded are 10 and 11 since they belong to the same MemID (1). I know how to join the tables and retrieve the records from the 2 tables

(Here is the query:
select t.TransID, t.MemID, m.lname, m.fname, t.code, t.fee from tblTransactions t, tblMembers m where m.memid = t.memid)

but what will be the code in the "WHERE" clause that will meet the condition that I need?

Thank you in advance,

Assaf!

Answer
Dear Assaf,

Here you would need to use either subquery or self join.  As there's only one column to join two queries or tables (member id), subquery would be the easiest one to use.

with subquery:
##############
select t.TransID
, t.MemID
, m.lname
, m.fname
, t.code
, t.fee
from tblTransactions t
, tblMembers m
where m.memid = t.memid
AND m.memid NOT IN (SELECT DISTINCT memid from tblMembers WHERE CODE = 'WEL-110')
##############


with self join:
##############
select t.TransID
, t.MemID
, m.lname
, m.fname
, t.code
, t.fee
from tblTransactions t
INNER JOIN tblMembers m
ON m.memid = t.memid
INNER JOIN tblMembers m1
ON m.memid = m1.memid
AND m1.CODE <> 'WEL-110'
##############


Feel free to follow-up.

Thanks,

MS SQL

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jwalant Natvarlal Soneji

Expertise

Questions related to query optimization, finding complex results.

Experience

Experience in the area: I have been working in the field since 2005 year. Education/Credentials: BE IT, First Class, 2005 Batch.

Publications
http://jwalantsoneji.com

Education/Credentials
BE IT, India

Awards and Honors
MCP

©2016 About.com. All rights reserved.