I'm having trouble subtracting groups from other groups.
I've got a data model that has the following essential features:
create table contacts (num int, properties....);
create table groups (groupNum int, contactNum int);
Where not all contacts will be in a group, some groups will contain most contacts, and
there will be something like hundreds of groups and tens of thousands of contacts. I
allow people to build groups using criteria, which I need to programatically translate
to sql.
One somewhat common pattern is:
Select all contacts in group a, who have property b, and who aren't in groups
c,d,e,f...
My first shot was subqueries:
select num, p1,p2 ... from contacts
inner join groups using (contacts.num=groups.contactNum)
where groups.groupNum=a
and contact.p3=b
and not num in (select contactNum from groups where groupNum=c)
and not num in (select contactNum from groups where groupNum=d)
and not num in (select contactNum from groups where groupNum=e)
and not num in (select contactNum from groups where groupNum=f)
This is .... slow. agonizingly so.
With an inner join, I'm not convinced that the subtraction is actually correct., but
it is much faster. Unfortunatley, faster incorrect answers are rarely helpful.
Outer joins seem even worse than subselects for speed, but it does appear to give the
correct answer. (example with a single join.)
select num from contacts
left outer join groups
on (contacts.num=groups.contactNum
and groups.groupNum=b)
where
dl_groupDonor._groupNum is null
and p3=c
I've got to be missing something here, because this is much slower from the (slow)
procedural system that I'm porting from.
I've been avoiding using union / intersect since I don't really ever know what columns
are going to be in the query. perhaps I should revisit that decision and try to work
around it.
eric
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
