SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict JamieJelly's Avatar
    Join Date
    Jan 2004
    Location
    London
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation SQL Query help required

    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
    International calls from the UK
    Cheap International Calls

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A simple join where you reference the agents back to the marketing company will do it for you.

  3. #3
    SitePoint Addict JamieJelly's Avatar
    Join Date
    Jan 2004
    Location
    London
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry to be a pain, could you give an example?

    thanks
    International calls from the UK
    Cheap International Calls

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
     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

  5. #5
    SitePoint Addict JamieJelly's Avatar
    Join Date
    Jan 2004
    Location
    London
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    International calls from the UK
    Cheap International Calls

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •