I know there is plenty of info for this, however I'm having some
trouble, and would love if someone could give me some info.

I want to tag objects in an "contacts" table.

I can have a separate table called "tags", and have a 1:n
relationship.

contacts: id, name
tags: contact_id, name

Now my question, how could I possibly select a contact that has both
the tag "Investor" AND "San Francisco"?

OR is easy:

SELECT contacts.name FROM contacts LEFT JOIN tags ON contacts.id =
tags.contact_id WHERE tags.name = "Investor" OR tags.name = "San
Francisco"

But AND does not work in this case:

SELECT contacts.name FROM contacts LEFT JOIN tags ON contacts.id =
tags.contact_id WHERE tags.name = "Investor" AND tags.name = "San
Francisco"


So how would I do a AND? I'd prefer not to do a COUNT/GROUP BY/
HAVING solution (too complicated), is that the only way?



thanks,
Herb

>I know there is plenty of info for this, however I'm having some
>trouble, and would love if someone could give me some info.
>
>I want to tag objects in an "contacts" table.
>
>I can have a separate table called "tags", and have a 1:n
>relationship.
>
>contacts: id, name
>tags: contact_id, name
>
>Now my question, how could I possibly select a contact that has both
>the tag "Investor" AND "San Francisco"?

Do a multi-way join between 'contacts' and N copies of the 'tags' table.
You have to use aliases for this, since you have to refer separately
to different instances of the tags table.

SELECT ...whatever... FROM contacts, tags t1, tags t2 ...

t1 is the tags entry with "Investor".
t2 is the tags entry with "San Francisco".

SELECT contacts.id, contacts.name from contacts, tags t1, tags t2
WHERE t1.contact_id = contacts.id AND t1.name = 'Investor' AND
t2.contact_id = contacts.id AND t2.name = 'San Francisco';
(not tested).

This rapidly gets complicated as looking for N tags requires an N+1-way
join. You can use the same approach with LEFT JOIN and ON clauses to
factor in a tag you DON'T want the results to have.

On Dec 30, 11:15 pm, gordonb.10...@burditt.org (Gordon Burditt) wrote:
> >I know there is plenty of info for this, however I'm having some
> >trouble, and would love if someone could give me some info.
>
> >I want to tag objects in an "contacts" table.
>
> >I can have a separate table called "tags", and have a 1:n
> >relationship.
>
> >contacts: id, name
> >tags: contact_id, name
>
> >Now my question, how could I possibly select a contact that has both
> >the tag "Investor" AND "San Francisco"?
>
> Do a multi-way join between 'contacts' and N copies of the 'tags' table.
> You have to use aliases for this, since you have to refer separately
> to different instances of the tags table.
>
> SELECT ...whatever... FROM contacts, tags t1, tags t2 ...
>
> t1 is the tags entry with "Investor".
> t2 is the tags entry with "San Francisco".
>
> SELECT contacts.id, contacts.name from contacts, tags t1, tags t2
> WHERE t1.contact_id = contacts.id AND t1.name = 'Investor' AND
> t2.contact_id = contacts.id AND t2.name = 'San Francisco';
> (not tested).
>
> This rapidly gets complicated as looking for N tags requires an N+1-way
> join. You can use the same approach with LEFT JOIN and ON clauses to
> factor in a tag you DON'T want the results to have.

That's a novel solution, thanks Gordon. Anyone else knows another
way, maybe even easier?

On Mon, 31 Dec 2007 20:01:13 +0100, herbasher <robmnl@gmail.com> wrote:

> On Dec 30, 11:15 pm, gordonb.10...@burditt.org (Gordon Burditt) wrote:
>> >I know there is plenty of info for this, however I'm having some
>> >trouble, and would love if someone could give me some info.
>>
>> >I want to tag objects in an "contacts" table.
>>
>> >I can have a separate table called "tags", and have a 1:n
>> >relationship.
>>
>> >contacts: id, name
>> >tags: contact_id, name
>>
>> >Now my question, how could I possibly select a contact that has both
>> >the tag "Investor" AND "San Francisco"?
>>
>> Do a multi-way join between 'contacts' and N copies of the 'tags' table.
>> You have to use aliases for this, since you have to refer separately
>> to different instances of the tags table.
>>
>> SELECT ...whatever... FROM contacts, tags t1, tags t2 ...
>>
>> t1 is the tags entry with "Investor".
>> t2 is the tags entry with "San Francisco".
>>
>> SELECT contacts.id, contacts.name from contacts, tags t1, tags t2
>> WHERE t1.contact_id = contacts.id AND t1.name = 'Investor' AND
>> t2.contact_id = contacts.id AND t2.name = 'San Francisco';
>> (not tested).
>>
>> This rapidly gets complicated as looking for N tags requires an N+1-way
>> join. You can use the same approach with LEFT JOIN and ON clauses to
>> factor in a tag you DON'T want the results to have.
>
> That's a novel solution, thanks Gordon. Anyone else knows another
> way, maybe even easier?

A dirty hack could be as followed: a combination of contect_id & name in
your tags table MUST be unique for this, 3 is the number here of tags to
match:

SELECT contact_id, COUNT(*) as 'matchcount'
FROM tags
WHERE name IN ('foo','bar','foz')
GROUP BY contact_id
HAVING matchcount = 3

or, for 4 tags:

SELECT contact_id, COUNT(*) as 'matchcount'
FROM tags
WHERE name IN ('foo','bar','foz','baz')
GROUP BY contact_id
HAVING matchcount = 4

It's not a very nice one, and it assumes a lot, however it could work.

--
Rik Wasmus

On Dec 31 2007, 2:27 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com>
wrote:
> On Mon, 31 Dec 2007 20:01:13 +0100, herbasher <rob...@gmail.com> wrote:
> > On Dec 30, 11:15 pm, gordonb.10...@burditt.org (Gordon Burditt) wrote:
> >> >I know there is plenty of info for this, however I'm having some
> >> >trouble, and would love if someone could give me some info.
>
> >> >I want to tag objects in an "contacts" table.
>
> >> >I can have a separate table called "tags", and have a 1:n
> >> >relationship.
>
> >> >contacts: id, name
> >> >tags: contact_id, name
>
> >> >Now my question, how could I possibly select a contact that has both
> >> >the tag "Investor" AND "San Francisco"?
>
> >> Do a multi-way join between 'contacts' and N copies of the 'tags' table.
> >> You have to use aliases for this, since you have to refer separately
> >> to different instances of the tags table.
>
> >> SELECT ...whatever... FROM contacts, tags t1, tags t2 ...
>
> >> t1 is the tags entry with "Investor".
> >> t2 is the tags entry with "San Francisco".
>
> >> SELECT contacts.id, contacts.name from contacts, tags t1, tags t2
> >> WHERE t1.contact_id = contacts.id AND t1.name = 'Investor' AND
> >> t2.contact_id = contacts.id AND t2.name = 'San Francisco';
> >> (not tested).
>
> >> This rapidly gets complicated as looking for N tags requires an N+1-way
> >> join. You can use the same approach with LEFT JOIN and ON clauses to
> >> factor in a tag you DON'T want the results to have.
>
> > That's a novel solution, thanks Gordon. Anyone else knows another
> > way, maybe even easier?
>
> A dirty hack could be as followed: a combination of contect_id & name in
> your tags table MUST be unique for this, 3 is the number here of tags to
> match:
>
> SELECT contact_id, COUNT(*) as 'matchcount'
> FROM tags
> WHERE name IN ('foo','bar','foz')
> GROUP BY contact_id
> HAVING matchcount = 3
>
> or, for 4 tags:
>
> SELECT contact_id, COUNT(*) as 'matchcount'
> FROM tags
> WHERE name IN ('foo','bar','foz','baz')
> GROUP BY contact_id
> HAVING matchcount = 4
>
> It's not a very nice one, and it assumes a lot, however it could work.
>
> --
> Rik Wasmus

Thanks Rik, I knew that one.

Seems like SQL is really not that perfect for certain situations.

Appreciated,
Rob