delete a record

SQL Server Developer >> delete a record

I have 2 tables: Member and GroupBelong

Member has 2 fields: memberID and MemberName
GroupBelong has 2 fields memberID and groupID

The members belong to several groups. They are joined by the memberID.
I need to delete the members who do not belong to ANY group????

--

______________________
David Fetrow
HelixPoint LLC.
http://www.hide-link.com/

Interested in Affordable Email Marketing?
Check out the HelixMailer at http://www.hide-link.com/
If you are interested in becoming a Reseller of HelixPoint products, contact

______________________

SQL Server21

SQL Server Developer >> delete a record delete from member where memberid not in (select memberid from groupbelong)

> I have 2 tables: Member and GroupBelong
>
> Member has 2 fields: memberID and MemberName
> GroupBelong has 2 fields memberID and groupID
>
> The members belong to several groups. They are joined by the memberID.
> I need to delete the members who do not belong to ANY group????
>
> --
>
> ______________________
> David Fetrow
> HelixPoint LLC.
> http://www.helixpoint.com

>
> Interested in Affordable Email Marketing?
> Check out the HelixMailer at http://www.helixpoint.com/helixmailer.asp
> If you are interested in becoming a Reseller of HelixPoint products,
contact

> ______________________
>
>

SQL Server Developer >> delete a record Here is one method:
===
DELETE FROM members WHERE NOT EXISTS
(
SELECT g.memberID FROM groupBelong g WHERE
g.memberID = members.memberID
)
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp

> I have 2 tables: Member and GroupBelong
>
> Member has 2 fields: memberID and MemberName
> GroupBelong has 2 fields memberID and groupID
>
> The members belong to several groups. They are joined by the memberID.
> I need to delete the members who do not belong to ANY group????
>
> --
>
> ______________________
> David Fetrow
> HelixPoint LLC.
> http://www.helixpoint.com

>
> Interested in Affordable Email Marketing?
> Check out the HelixMailer at http://www.helixpoint.com/helixmailer.asp
> If you are interested in becoming a Reseller of HelixPoint products,
contact

> ______________________
>
>

SQL Server Developer >> delete a record As an FYI,

The first method offered by Anthony Faull will be far
more efficient than the second offered by SriSamp.

>-----Original Message-----
>I have 2 tables: Member and GroupBelong
>
>Member has 2 fields: memberID and MemberName
>GroupBelong has 2 fields memberID and groupID
>
>The members belong to several groups. They are joined by
the memberID.
>I need to delete the members who do not belong to ANY
group????
>
>--
>
>______________________
>David Fetrow
>HelixPoint LLC.
>http://www.helixpoint.com

>
>Interested in Affordable Email Marketing?
>Check out the HelixMailer at
http://www.helixpoint.com/helixmailer.asp
>If you are interested in becoming a Reseller of
HelixPoint products, contact

>______________________
>
>
>.
>

SQL Server Developer >> delete a record What makes you think so?

/T

> As an FYI,
>
> The first method offered by Anthony Faull will be far
> more efficient than the second offered by SriSamp.
>
> >-----Original Message-----
> >I have 2 tables: Member and GroupBelong
> >
> >Member has 2 fields: memberID and MemberName
> >GroupBelong has 2 fields memberID and groupID
> >
> >The members belong to several groups. They are joined by
> the memberID.
> >I need to delete the members who do not belong to ANY
> group????
> >
> >--
> >
> >______________________
> >David Fetrow
> >HelixPoint LLC.
> >http://www.helixpoint.com

> >
> >Interested in Affordable Email Marketing?
> >Check out the HelixMailer at
> http://www.helixpoint.com/helixmailer.asp
> >If you are interested in becoming a Reseller of
> HelixPoint products, contact

> >______________________
> >
> >
> >.
> >

SQL Server Developer >> delete a record Explain thyself oh anonymous giver of opinion. You may be right (I see
possibilites on each side) but it will all come down to how well the in
clause is dealt with. Either way it is best to explain so a newbie doesn't
get the idea that EXISTS is better than IN in all cases.

To DaveF, did you try both, which was faster?

--
----------------------------------------------------------------------------
-----------

Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

> As an FYI,
>
> The first method offered by Anthony Faull will be far
> more efficient than the second offered by SriSamp.
>
> >-----Original Message-----
> >I have 2 tables: Member and GroupBelong
> >
> >Member has 2 fields: memberID and MemberName
> >GroupBelong has 2 fields memberID and groupID
> >
> >The members belong to several groups. They are joined by
> the memberID.
> >I need to delete the members who do not belong to ANY
> group????
> >
> >--
> >
> >______________________
> >David Fetrow
> >HelixPoint LLC.
> >http://www.helixpoint.com

> >
> >Interested in Affordable Email Marketing?
> >Check out the HelixMailer at
> http://www.helixpoint.com/helixmailer.asp
> >If you are interested in becoming a Reseller of
> HelixPoint products, contact

> >______________________
> >
> >
> >.
> >

SQL Server Developer >> delete a record How do I tell how fast they are??

> Explain thyself oh anonymous giver of opinion. You may be right (I see
> possibilites on each side) but it will all come down to how well the in
> clause is dealt with. Either way it is best to explain so a newbie
doesn't
> get the idea that EXISTS is better than IN in all cases.
>
> To DaveF, did you try both, which was faster?
>
> --
> --------------------------------------------------------------------------
--
> -----------

> Compass Technology Management
>
> Pro SQL Server 2000 Database Design
> http://www.apress.com/book/bookDisplay.html?bID=266
>
> Note: Please reply to the newsgroups only unless you are
> interested in consulting services. All other replies will be ignored :)
>

> > As an FYI,
> >
> > The first method offered by Anthony Faull will be far
> > more efficient than the second offered by SriSamp.
> >
> > >-----Original Message-----
> > >I have 2 tables: Member and GroupBelong
> > >
> > >Member has 2 fields: memberID and MemberName
> > >GroupBelong has 2 fields memberID and groupID
> > >
> > >The members belong to several groups. They are joined by
> > the memberID.
> > >I need to delete the members who do not belong to ANY
> > group????
> > >
> > >--
> > >
> > >______________________
> > >David Fetrow
> > >HelixPoint LLC.
> > >http://www.helixpoint.com

> > >
> > >Interested in Affordable Email Marketing?
> > >Check out the HelixMailer at
> > http://www.helixpoint.com/helixmailer.asp
> > >If you are interested in becoming a Reseller of
> > HelixPoint products, contact

> > >______________________
> > >
> > >
> > >.
> > >
>
>

SQL Server Developer >> delete a record Run them :)

Seriously, two things I like to do:

1. Run individually within a transaction (a couple of times) Check the
times
2. Run both side by side and look at the plans. See which one has the
larger percentage of cost.

If they both run nearly the same, then who cares. Anthony's is a bit easier
to digest, but both should do the job.

--
----------------------------------------------------------------------------
-----------

Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

> How do I tell how fast they are??
>
>
>

> > Explain thyself oh anonymous giver of opinion. You may be right (I see
> > possibilites on each side) but it will all come down to how well the in
> > clause is dealt with. Either way it is best to explain so a newbie
> doesn't
> > get the idea that EXISTS is better than IN in all cases.
> >
> > To DaveF, did you try both, which was faster?
> >
> > --
>
> --------------------------------------------------------------------------
> --
> > -----------

> > Compass Technology Management
> >
> > Pro SQL Server 2000 Database Design
> > http://www.apress.com/book/bookDisplay.html?bID=266
> >
> > Note: Please reply to the newsgroups only unless you are
> > interested in consulting services. All other replies will be ignored :)
> >

> > > As an FYI,
> > >
> > > The first method offered by Anthony Faull will be far
> > > more efficient than the second offered by SriSamp.
> > >
> > > >-----Original Message-----
> > > >I have 2 tables: Member and GroupBelong
> > > >
> > > >Member has 2 fields: memberID and MemberName
> > > >GroupBelong has 2 fields memberID and groupID
> > > >
> > > >The members belong to several groups. They are joined by
> > > the memberID.
> > > >I need to delete the members who do not belong to ANY
> > > group????
> > > >
> > > >--
> > > >
> > > >______________________
> > > >David Fetrow
> > > >HelixPoint LLC.
> > > >http://www.helixpoint.com

> > > >
> > > >Interested in Affordable Email Marketing?
> > > >Check out the HelixMailer at
> > > http://www.helixpoint.com/helixmailer.asp
> > > >If you are interested in becoming a Reseller of
> > > HelixPoint products, contact

> > > >______________________
> > > >
> > > >
> > > >.
> > > >
> >
> >
>
>