minus in mysql problem

[milas]

i have three table member (memberid,m_name,statusid,address,DOB,phone) booking (bookingid, memberid ,sessionid, cost) session(sessionid, activity,location)

(fk memberid, fk sessionid )BOOKING IS link with ( member pk )and (session pk)

session activity is like football,swimming,badminton,tennis i want to run the following query
find the name of all member who have booked any session for 'football', but not any session for 'swimming' during december 2009
sql

select DISTINCT Event1.m_name ,Event1.activity
FROM ( select mm.m_name,ss.activity,ss.sessionid,ss.s_date from member mm inner join booking bb on bb.memberid = mm.memberid inner join session ss on ss.sessionid = bb.sessionid where activity = 'football' )as Event1

INNER JOIN 
( select mm.m_name ,ss.activity,ss.sessionid,ss.s_date from member mm inner join booking bb on bb.memberid = mm.memberid inner join session ss on ss.sessionid = bb.sessionid where mm.memberid not in ( select mm.memberid from member mm inner join booking bb on bb.bookingid = mm.memberid inner join session ss on bb.sessionid = ss.sessionid where ss.activity in ( 'swimming') )

 ) as Event2

on Event1.sessionid = Event2.sessionid

what im doing wrong it return me memeber who have book football and swmming.

[BitBlt]

I'm making a couple of assumptions about your table defs and data just for the sake of testing, but here's what I came up with:

select a.m_name, b.bookingdate, c.*
from dbo.member a
inner join dbo.booking b
    on a.memberid = b.memberid
inner join dbo.session c
    on b.sessionid = c.sessionid
where c.activity = 'football'
and month(b.bookingdate) = 12
and year(b.bookingdate) = 2009
and not exists
(
select 1 
from dbo.booking b1
inner join dbo.session c1
    on b1.sessionid = c1.sessionid
where memberid = a.memberid
and c1.activity = 'swimming'
and month(b1.bookingdate) = 12
and year(b1.bookingdate) = 2009
)

Important points:
1. Joining tables to find everyone who signed up for football.
2. Added a "booking date" to your "booking" table so you could find December. I could have put it in the "session" table just as easily, but for some reason I didn't.
3. Using "not exists" in the "where" clause.
4. Correlated sub-select inside the "not exists" joining the memberid inside to the memberid outside.
5. Not returning any data in the "not exists". This prevents you from having to actually spend system resources retrieving data you'll never actually use.

Hope this helps! I'll post all my table creates/data inserts as a separate post.

[BitBlt]

Here's the set of statements I used to create tables and insert test data. Feel free to use or not to validate my results.

create table dbo.member 
(memberid int identity(1, 1) not null,
m_name varchar(25) not null,
statusid int not null,
address varchar(25) null,
DOB datetime null,
phone varchar(12) null,
constraint PK_Member primary key (memberid)
) 
go

create table dbo.session
(sessionid int identity(1, 1) not null, 
activity varchar(25) null,
location varchar(25) null,
constraint PK_Session primary key (sessionid)  
)
go

create table dbo.booking 
(bookingid int identity(1, 1) not null, 
memberid int not null,
sessionid int not null, 
bookingdate datetime not null,
cost money not null,
constraint PK_Booking primary key (bookingid),
constraint FK_Member foreign key (memberid) references dbo.member (memberid),
constraint FK_Session foreign key (sessionid) references dbo.session (sessionid)  
) 
go

insert into dbo.member (m_name, statusid, address, DOB, phone)
values ('Joe', 1, '123 Fourth St.', '1/2/1993', '111-222-3333')

insert into dbo.member (m_name, statusid, address, DOB, phone)
values ('Bill', 1, '456 Seventh St.', '3/4/1993', '444-555-6666')

insert into dbo.member (m_name, statusid, address, DOB, phone)
values ('Dave', 1, '8910 Eleventh St.', '5/6/1993', '777-888-9999')

insert into dbo.member (m_name, statusid, address, DOB, phone)
values ('Jane', 1, '123 Main St.', '1/2/1993', '111-222-3333')

insert into dbo.member (m_name, statusid, address, DOB, phone)
values ('Beth', 1, '456 Maple St.', '3/4/1993', '444-555-6666')

insert into dbo.member (m_name, statusid, address, DOB, phone)
values ('Donna', 1, '8910 Broad St.', '5/6/1993', '777-888-9999')

insert into dbo.session (activity, location)
values ('football', 'Football Field')

insert into dbo.session (activity, location)
values ('swimming', 'Pool')

insert into dbo.session (activity, location)
values ('badminton', 'Indoor Gym')

insert into dbo.session (activity, location)
values ('tennis', 'Tennis Courts')

insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (1, 1, '12/12/2009', 100.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (2, 1, '12/12/2009', 100.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (3, 1, '12/12/2009', 100.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (4, 1, '12/12/2009', 100.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (5, 1, '12/12/2009', 100.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (1, 2, '12/12/2009', 20.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (2, 2, '12/12/2009', 20.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (3, 2, '12/12/2009', 20.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (1, 3, '12/12/2009', 30.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (2, 3, '12/12/2009', 30.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (3, 3, '12/12/2009', 30.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (4, 3, '12/12/2009', 30.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (5, 3, '12/12/2009', 30.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (6, 3, '12/12/2009', 30.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (1, 4, '12/12/2009', 10.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (2, 4, '12/12/2009', 10.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (3, 4, '12/12/2009', 10.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (4, 4, '12/12/2009', 10.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (5, 4, '12/12/2009', 10.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (6, 4, '12/12/2009', 10.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (1, 1, '11/11/2009', 100.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (2, 1, '11/11/2009', 100.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (3, 1, '11/11/2009', 100.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (4, 1, '11/11/2009', 100.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (5, 1, '11/11/2009', 100.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (6, 1, '11/11/2009', 100.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (1, 2, '11/11/2009', 20.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (2, 2, '11/11/2009', 20.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (3, 2, '11/11/2009', 20.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (4, 2, '11/11/2009', 30.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (5, 2, '11/11/2009', 30.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (6, 2, '11/11/2009', 30.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (1, 3, '11/11/2009', 30.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (2, 3, '11/11/2009', 30.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (3, 3, '11/11/2009', 30.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (4, 3, '11/11/2009', 30.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (5, 3, '11/11/2009', 30.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (6, 3, '11/11/2009', 30.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (1, 4, '11/11/2009', 10.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (2, 4, '11/11/2009', 10.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (3, 4, '11/11/2009', 10.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (4, 4, '11/11/2009', 10.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (5, 4, '11/11/2009', 10.00)
insert into dbo.booking(memberid, sessionid, bookingdate, cost) values (6, 4, '11/11/2009', 10.00)