Multiple table search .......

[rab54]

OK Gurus -

I have two tables - one 'postcodes' the other 'agents' -

I can currently list a count of all agents in the 'postcodes' table -
but the agents are only added to the 'postcodes' table when a record is inserted -

How do I get a count to give me the agents that are NOT in the 'postcodes' table ?

eg

Postcode table ---- 001 agent0001 record info
---- 002 agent0001 record info

Agents table ----- 001 agent0001name
----- 002 agent0002name

count will show ----- agents total
agent0001 2
agent0002 0

Hope you can help ......

cheers

Rab

 

[TonyGroves]

Sorry Rab, I can't figure out the structure of your tables. Maybe you could make it a bit clearer.

 

[rab54]

Sorry Tony - I'll try again -

postcodes table -

id | postcode | agent | date_entered etc ....

agents table -

agent_id | agent_name

When a record is added to the post_code table the agent name is added - this comes from a seperate web-page -

At the end of the day we may have 200 new records in the postcodes table -

I can run a count (grouped on Agent from this) - but I need the count to include the agents (already in the agents table) that have NO entries in the postcode table -

At present I only get the agents that HAVE got entries in the postcode table (I do realise that the agents from the agents table will all be zero tho ;-)

cheers for your help

Rab

 

[TonyGroves]

How about:
[tt]
SELECT agents.agent_id,count(*) total
FROM
agents JOIN postcodes ON postcodes.agent=agents.agent_id
GROUP BY agents.agent_id
UNION
SELECT agents.agent_id,0
FROM
agents JOIN postcodes ON postcodes.agent=agents.agent_id
WHERE postcodes.agent is NULL
GROUP BY agents.agent_id
[/tt]
However, that will put the zero-count agents at the end of the result set.
If you want it sorted by agent_id, then maybe the following:
[tt]
CREATE TEMPORARY TABLE t AS
SELECT agents.agent_id,count(*) total
FROM
agents JOIN postcodes ON postcodes.agent=agents.agent_id
GROUP BY agents.agent_id
UNION
SELECT agents.agent_id,0
FROM
agents LEFT JOIN postcodes ON postcodes.agent=agents.agent_id
WHERE postcodes.agent is NULL
GROUP BY agents.agent_id

SELECT * from t ORDER BY agent_id

DROP TABLE t
[/tt]