Query/Table Join

Hey guys,

I need to perform a query on two tables that look like this:

ATTENDANCE
attendanceid
memberid
meetingid
meetingdate

MEMBERS
memberid
firstname
lastaname

I can run this query just fine:

select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid = a.memberid

This gives me memberid's for members that are present.

I need members that are not present.

select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid <> a.memberid

This returns a thousand rows when it should return no more than 25.

I am sure this is just a simple join but I do not have my SQL for dummies book with me at the moment ;-)

Thanks,

MikeI guess this is what u r trying for.

select memberid from members where memberid not in(select memberid from attendance where meetingid = 47)

Originally posted by mycoolross
Hey guys,

I need to perform a query on two tables that look like this:

ATTENDANCE
attendanceid
memberid
meetingid
meetingdate

MEMBERS
memberid
firstname
lastaname

I can run this query just fine:

select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid = a.memberid

This gives me memberid's for members that are present.

I need members that are not present.

select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid <> a.memberid

This returns a thousand rows when it should return no more than 25.

I am sure this is just a simple join but I do not have my SQL for dummies book with me at the moment ;-)

Thanks,

Mike|||THANK YOU!!

Query to linked server:Oracle; problems with ANSI_NULLS;ANSI_WARNINGS

When I perform a query on a linked Oracle server in the Query analyser I
have no
prboblem' to perform this query.
However, when I create this query in a stored procedure I get a compilation
error
when saving this procedure. (Not when compiling; it has no errors)

Server: Msg 7405, Level 16, State 1, Line 1
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options
to be set for the connection. This ensures consistent query semantics.
Enable these options and then reissue your query.

When I create a dynamic SQL statement then I can save this stored procedure
when I run the stored procedure this same error happens.

What do I have to do.

Arno de Jong, The Netherlands"A.M. de Jong" <arnojo@.wxs.nl> wrote in message
news:bq9lqp$qag$1@.reader11.wxs.nl...
> When I perform a query on a linked Oracle server in the Query analyser I
> have no
> prboblem' to perform this query.
> However, when I create this query in a stored procedure I get a
compilation
> error
> when saving this procedure. (Not when compiling; it has no errors)
> Server: Msg 7405, Level 16, State 1, Line 1
> Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options
> to be set for the connection. This ensures consistent query semantics.
> Enable these options and then reissue your query.
> When I create a dynamic SQL statement then I can save this stored
procedure
> when I run the stored procedure this same error happens.
> What do I have to do.
> Arno de Jong, The Netherlands

Have you tried putting SET ANSI_NULLS ON and SET ANSI_WARNINGS ON at the
start of your stored procedure, ie. in the procedure code itself? QA sets
these on automatically, but other connections may not.

Simon