4 tables - subqueries or joins

[railboy]

Hi I would really appreciate some help on this one as I've tried various methods and can't get this to work on MySQL.
Any ideas anyone ?

I have 4 tables: agents, contacts, subscription, mailinglists

agents
ID_AGENT int(11) 0
NAME varchar(32)
.....

contacts
ID_CONTACT smallint(6) PRI (NULL) auto_increment
NAME char(32)
ID_AGENT int(11) YES (NULL)
.....

subscription
ID_CONTACT smallint(6) PRI 0
ID_MAILING bigint(20) PRI 0
....

mailinglists
ID_MAILING bigint(20) PRI (NULL) auto_increment
NAME_LIST varchar(32) YES (NULL)
....

A contact is not necessarily a memeber of an agent.
I am trying to find out all the agents that don't have any members subscribed to a certain mailing list X.
For this example I am putting X=5

I have never used subqueries but as I have MySQL 4.0.15 I think it should support them.

select agent.id_agent, agent.name
from agent
where agent.id_agent <>(select agent.id_agent from agent GROUP BY id_agent
join contact, subscription on contact.id_contact=subscription.id_contact
where subscription.id_mailing='5' AND contact.id_agent=agent.id_agent)

I thought that this would work... but I get the following error:

Error Code : 1064
You have an error in your SQL syntax.
Check the manual that corresponds to your MySQL server version for the right syntax to use
near 'select agent.id_agent from agent GROUP BY id_agent
join contact, subscr
(15 ms taken)

It obviously has to do with the pre-subquery syntax as the subquery itself works fine when alone....

Maybe it can be done with joins but I can't get my head around it.

I would greatly appreciate it if someone could point me in the right direction.

Thanks.
railboy

 

[ggggus]

select agents.ID_AGENT,agents.name,contacts.ID_CONTACT
from agents
left join contacts on contacts.ID_AGENT=agents.ID_AGENT
left join subscription on contacts.ID_CONTACT=subscription.ID_CONTACT and subscription.ID_MAILING=1
where contacts.ID_CONTACT IS NULL;

I think that should work for you.

***************************************
J. Jacobs

http://www.piercedmedia.com

 

[ggggus]

select agents.ID_AGENT,agents.name,subscription.ID_CONTACT
from agents left join contacts on contacts.ID_AGENT=agents.ID_AGENT
left join subscription on contacts.ID_CONTACT=subscription.ID_CONTACT and subscription.ID_MAILING=1
where subscription.ID_CONTACT IS NULL;

I ran another test and realized that this will work better...the previous one allows a loop hole, this should get more complete results.

***************************************
J. Jacobs

http://www.piercedmedia.com

 

[railboy]

Thanks ggggus but I don't think he query is correct, when I test it on my data I get an agent appearing and he shouldn't as he has several contacts subscribed to that list (mailinglist '1' in your example).

 

[railboy]

Still don't think that this solution works, but I just wanted to point out that subequeries don't work for me as the version of MySQL is 4.0.15 and subqueries are supported from version 4.1 upwards.