Page 1 of 1

Complex SQL

Posted: Fri Jul 29, 2005 4:58 pm
by spartan7
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

Rephrase

Posted: Fri Jul 29, 2005 6:26 pm
by spartan7
Let me rephrase to make what Im saying more understandable...

If I wanted to search for a Contact (in Contacts Table) that is linked via GroupContacts Table to Group:Hotel and Group:Tours (in Groups Table)

How would I retrieve that contact?
---
FROM webcontacts, webgroupcontacts, webgroups
WHERE webcontacts.ContactID = webgroupcontacts.ContactID
AND webgroupcontacts.GroupID = webgroups.GroupID
AND webgroups.GroupIC = 'Hotel'
AND webgroups.GroupIC = 'Tours'

Retrieves nothing?

I only want Contacts that are in Hotels AND Tours. I dont want all the Contacts in Hotels or Tours.

How do I do it?

Posted: Fri Jul 29, 2005 6:36 pm
by nielsene
Here's a slightly simplified version of your first example:

Code: Select all

SELECT DISTINCT * 
  FROM webcontacts NATURAL JOIN 
       webgroupcontacts NATURAL JOIN
       webgroups 
WHERE webgroups.CategoryID = 'accommodation'
Now for the second:

Code: Select all

SELECT DISTINCT * 
  FROM webcontacts NATURAL JOIN 
       webgroupcontacts AS c JOIN
       webgroups AS g1 ON( c.GroupID=g1.GroupID) JOIN
       webgroups AS g2 ON (c.GroupID=g2.GroupID)
WHERE webgroups.CategoryID = 'accommodation' AND
      g1.GroupIC='Hotels' AND
      g2.GroupIC='Tours'

The query works but..,

Posted: Fri Jul 29, 2005 9:03 pm
by spartan7
Hi there, the query works but I still get nothing.

I know for a fact a certain Contact is in both Hotels and Tours. Hotels is under Accommodation Category and Tours is not. I get no results from that query.

Thanks

Posted: Fri Jul 29, 2005 9:10 pm
by nielsene
OK I'm not certain of your data layout enough to know, but this might do it:

Code: Select all

SELECT DISTINCT * 
  FROM webcontacts AS w JOIN   
       webgroupcontacts AS c1 ON (w.ContactID=c1.ContactID) JOIN
       webgroupcontacts AS c2 ON (w.ContactID=c2.ContactID) JOIN
       webgroups AS g1 ON( c1.GroupID=g1.GroupID) JOIN
       webgroups AS g2 ON (c2.GroupID=g2.GroupID)
WHERE webgroups.CategoryID = 'accommodation' AND
      g1.GroupIC='Hotels' AND
      g2.GroupIC='Tours'

Hi there that seems to work

Posted: Fri Jul 29, 2005 10:32 pm
by spartan7
Hi there, that seems to work.

Can you explain briefly what you did there? You used aliases to get it to search the table twice for both Groups?

Thanks

Posted: Fri Jul 29, 2005 11:02 pm
by nielsene
Sure.

You wanted to search for all contacts that had two keywords. The first keyword had to be "Hotel" underneath the "Accomodation" group. The second had to be "Tours" under an unspecified Category. As each row in the orginial query would only have on GroupIC column, I initally added the double join on webgroups to get the ability to search for two different GroupIC's for a given webcontact.

As you said that "Tours" is not under Accomodation, you have to first find contacts that have at least two different entries in webgroupcontacts. Thats the two join rows, in order to keep them straight you have to give them aliases, so I called one c1 and the other c2.
Now we want to find the list keywords for those groups, so each c1/c2 gets joined to its own version of the webgroups tablee. Then in the where clause I force the values you wanted.

Of course this gives a VERY wide row. You probabbly want to change the SELECT DISTINCT *
to SELECT DISTINCT w.columnname, c1.columnname, etc as needed to pare down the list of return columns to a more reasonable size.