Complex SQL
Posted: Fri Jul 29, 2005 4:58 pm
I have 3 tables - Contacts - GroupContacts - Groups
Contacts: ContactID Contact
GroupContacts: GroupContactID GroupID ContactID
Groups: GroupID CategoryID Group
Categories do not have a table, they just have a code in the Groups Table.
The main category chosen will filter what groups are displayed, and what results are displayed from the Contacts table.
Within the Groups table there is a further Category called Keywords.
This is what I have:
SELECT DISTINCT *
FROM contacts, groupcontacts, groups
WHERE webcontacts.ContactID = webgroupcontacts.ContactID
AND webgroupcontacts.GroupID = webgroups.GroupID
AND webgroups.CategoryID = 'accommodation'
That is to pull in all the Contacts under the Category Accommodation. The problem is how do I pull in the Contacts that are Category Accommodation and linked to Category Keywords?
The client wants me to first, filter the results by Category (say accommodation for example), and then Search those filtered results via a keyword (Groups with the Category Keyword), but the Keyword Groups/Category is in the same Table as the Category/Groups for Accommodation.
So I need to : Filter the results by Category (eg. Category Accommodation)
Next : Filter the Accommodation results by Keywords (eg. Category Keyword Group Tours)
How do I do it?
Thanks
Contacts: ContactID Contact
GroupContacts: GroupContactID GroupID ContactID
Groups: GroupID CategoryID Group
Categories do not have a table, they just have a code in the Groups Table.
The main category chosen will filter what groups are displayed, and what results are displayed from the Contacts table.
Within the Groups table there is a further Category called Keywords.
This is what I have:
SELECT DISTINCT *
FROM contacts, groupcontacts, groups
WHERE webcontacts.ContactID = webgroupcontacts.ContactID
AND webgroupcontacts.GroupID = webgroups.GroupID
AND webgroups.CategoryID = 'accommodation'
That is to pull in all the Contacts under the Category Accommodation. The problem is how do I pull in the Contacts that are Category Accommodation and linked to Category Keywords?
The client wants me to first, filter the results by Category (say accommodation for example), and then Search those filtered results via a keyword (Groups with the Category Keyword), but the Keyword Groups/Category is in the same Table as the Category/Groups for Accommodation.
So I need to : Filter the results by Category (eg. Category Accommodation)
Next : Filter the Accommodation results by Keywords (eg. Category Keyword Group Tours)
How do I do it?
Thanks