Hi,

I am new to SQL and trying to determine how to write a query to perform the
following:

I have a table that contains 2 columns: GroupCode and ContactCode, where
contacts can belong to several groups.

I would like to be able to select ContactCode where the contact is a member
of both groups 7 and 8, unless the contact is also a member of group 10.

I tried the following as I assumed the query would first generate the list
of contacts in both 7 & 8 and then remove those in 10, but this doesn't seem
to work.

SELECT ContactCode FROM ContactMembership WHERE (GroupCode = 7)
INTERSECT
SELECT ContactCode FROM ContactMembership WHERE (GroupCode = 8)
EXCEPT
SELECT ContactCode FROM ContactMembership WHERE (GroupCode = 10)

Any help appreciated.

Thanks
Jack

Jack


Try

SELECT ContactCode FROM ContactMembership WHERE GroupCode IN (7,8) and GroupCode <> 10

Roy Lambert

Hi Roy,

I tried your suggestion, but this appears to select contacts with either
group 8 OR group 9, (even if they're also in group 10).

I want to list contacts who have both 7 AND 8 membership, so long as they
don't also have group 10 membership.

for example, if the table contained the following records
(ContactCode,GroupCode):

1    7
1    8
2    7
2    8
2    10
3    7
4    7
4    8

I want the following ContactCode results to be generated by the query:

1
4

ContactCode = 2 would be excluded because they also belong to group 10
ContactCode = 3 would be excluded because although they belong to group 7,
they don't belong to group 8

Cheers,
Jack

"Jack Marsh" <jack@marshdata.co.uk> wrote in message
news:302710BA-4FA1-4099-9D91-2F1EC75A249A@news.elevatesoft.com...
> Hi,
>
> I am new to SQL and trying to determine how to write a query to perform
the
> following:
>
> I have a table that contains 2 columns: GroupCode and ContactCode, where
> contacts can belong to several groups.
>
> I would like to be able to select ContactCode where the contact is a
member
> of both groups 7 and 8, unless the contact is also a member of group 10.
>
> I tried the following as I assumed the query would first generate the list
> of contacts in both 7 & 8 and then remove those in 10, but this doesn't
seem
> to work.
>
> SELECT ContactCode FROM ContactMembership WHERE (GroupCode = 7)
> INTERSECT
> SELECT ContactCode FROM ContactMembership WHERE (GroupCode = 8)
> EXCEPT
> SELECT ContactCode FROM ContactMembership WHERE (GroupCode = 10)
>

SELECT ContactCode,
SUM(IF(GroupCode = 7 then 1 else 0)) Has7,
SUM(IF(GroupCode = 8 then 1 else 0)) Has8,
SUM(IF(GroupCode = 10 then 1 else 0)) Has10
Group by ContactCode
HAVING (Has7 > 0) and (Has8 > 0) and (Has10 = 0)

Robert

Jack,

<< I tried the following as I assumed the query would first generate the
list of contacts in both 7 & 8 and then remove those in 10, but this doesn't
seem to work. >>

select distinct ContactCode
from ContactMembership
where GroupCode in (7, 8)
except
select distinct ContactCode
from ContactMembership
where GroupCode = 10

Ole Willy Tuv

Thanks very much Robert, that was exactly what I was trying to achieve!

However, my table will be large and similar queries will be run frequently
by several concurrent users - will this method be fairly efficient?

I don't know much about how SQL (DBISAM) works - would your query require
that every record in the table be accessed and tested for the SUM
conditions?

Many of the groups will have very few members and I had hoped that DBISAM
would be able to use an index on the GroupCode column to:

(1). Filter only those records belonging to group 7
(2). Filter only those records belonging to group 8
(3). Isolate ContactCodes present in both stages (1) and (2) result sets
(INTERSECT)
(4). Filter only those records belonging to group 10
(5). Remove ContactCodes present at stage (4) from stage (3) (EXCEPT)

Does anyone have any views on whether or not this is approach is feasible or
indeed desirable?  If so, can it be performed in a single script or is it
necessary to break it into a series of SQL queries that get executed in
sequence?

Many thanks,
Jack

Jack,

> select distinct ContactCode
> from ContactMembership
> where GroupCode in (7, 8)
> except
> select distinct ContactCode
> from ContactMembership
> where GroupCode = 10

Sorry, I misread your requirements. You want the ContactCodes having rows
with *both* 7 and 8 in GroupCode, not either 7 or 8.

In that case, I believe your own query actually is correct. It seems that
DBISAM produces an incorrect result set here.

Robert's alternative should give you the correct result.

Ole

Thanks for your suggestion, but it doesn't give me the result I expected.

for example, if the table contained the following records
(ContactCode,GroupCode):

1    7
1    8
2    7
2    8
2    10
3    7
4    7
4    8

I want the following ContactCode results to be generated by the query:

1
4

ContactCode = 2 should be excluded because they also belong to group 10
ContactCode = 3 should be excluded because although they belong to group 7,
they don't belong to group 8

However, your query lists all four ContactCodes in its results table:

1
2
3
4

It looks like your query would accept membership of either groups 7 OR 8
rather than demand membership of both 7 AND 8, but I can't see why it
doesn't remove ContactCode=2 from the results due to it's membership of
group 10.

Any ideas?

Thanks
Jack


"Ole Willy Tuv" <owtuv@online.no> wrote in message
news:C4E3484C-D59D-46FB-AA00-2907E3C80C2C@news.elevatesoft.com...
> Jack,
>
> << I tried the following as I assumed the query would first generate the
> list of contacts in both 7 & 8 and then remove those in 10, but this
> doesn't seem to work. >>
>
> select distinct ContactCode
> from ContactMembership
> where GroupCode in (7, 8)
> except
> select distinct ContactCode
> from ContactMembership
> where GroupCode = 10
>
> Ole Willy Tuv
>
>

Jack,

<< ContactCode = 2 should be excluded because they also belong to group 10
ContactCode = 3 should be excluded because although they belong to group 7,
they don't belong to group 8 >>

Yes, sorry. As I said in another reply, I misread your requirements.

<< However, your query lists all four ContactCodes in its results table: >>

It returns the ConctactCodes 1, 3 and 4 here.

Your original query should give the result you want. I believe DBISAM is
producing an incorrect result with your query.

Ole

Jack


My mistake. I know you have a solution from Robert but here's a script which also produces the right result just so you can see a different way

select distinct ContactCode into memory\tmp1 from ContactMembership where ContactGroup = 7;
select distinct ContactCode into memory\tmp2 from ContactMembership where ContactGroup = 8;
select distinct ContactCode into memory\tmp3 from ContactMembership where ContactGroup = 10;

delete from memory\tmp1 where ContactCode not in (select ContactCode from tmp2);
delete from memory\tmp1 where ContactCode in (select ContactCode from tmp3);

select * from memory\tmp1;

Roy Lambert