SQL Query help required

[JamieJelly]

Hi all, I am having some trouble retrieving data from my DB. I was wondering if anyone could help?

The structure exists like this:

An marketing company table (primary key is marketing company name)
each marketing company has many agents. The agents are stored in the agent table. The marketing company they belong to is identified by the marketing company secondary key.

Each agent generates many sales leads. The sales leads "owners" are identified by the agent secondary key.

If I want to find out all the leads generated by an agent, this is simple, SELECT * FROM Leads WHERE agentname="agentname"

How about if I want to find every lead generated by a marketing company? How can I achieve this in a query?

I hope I explained the structure sufficiently.

Thanks

[guelphdad]

A simple join where you reference the agents back to the marketing company will do it for you.

[JamieJelly]

sorry to be a pain, could you give an example?

thanks

[guelphdad]

 select agenttable.agent, foo, bar
 from agenttable
 inner join 
 companytable
 on agenttable.companyname=companytable.companyname
 where agenttable.companyname='companyname'
 group by
 agenttable.agent, foo, bar

[JamieJelly]

hi Daveman, thanks for the reply, however this doesn't give me all the leads from one company, just the agents.

table structure
company_tbl
+++++++++
companyid |
+++++++++

agent_tbl
+++++++++
agentid |
companyid | secondary key
+++++++++

lead_tbl
+++++++++
leadid |
agentid | secondary key
+++++++++
I want to get all the leads from the lead table by the company.

thanks

[r937]

select leadid
  from company_tbl 
inner
  join agent_tbl 
    on company_tbl.companyid 
     = agent_tbl.companyid
inner  
  join lead_tbl
    on agent_tbl.agentid 
     = lead_tbl.agentid