SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast adear11's Avatar
    Join Date
    Oct 2003
    Location
    Richland MS
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    join 3 tables with no common field

    I very much need some help with a postgres query.

    I have 3 tables:

    domains(
    domain_id,
    domain_name
    )

    stores(
    store_id,
    store_name,
    domain_id
    )

    customer_prospects(
    customer_id,
    store_id,
    salesperson_id
    )

    I need a query that will will give me the number of customer_prospects in each domain like"

    "Select domain_name,count(customer_id) from ..."

    How to write this really has me stumped. I can use multiple queries and foreach loops, but if it can be done in a query I would rather do it that way.

    Please help!!

  2. #2
    SitePoint Wizard chris_fuel's Avatar
    Join Date
    May 2006
    Location
    Ventura, CA
    Posts
    2,750
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Assuming all stores have domains and all customer_prospects have stores, I'm assuming that the following should work:

    Code SQL:
    SELECT domain_name, COUNT(customer_id) AS customer_count 
    FROM domains
    INNER JOIN stores ON (stores.domain_id = domains.domain_id)
    INNER JOIN customer_prospects ON (stores.store_id = customer_prospects.store_id)
    WHERE domain_id = ?

    which assumes you're searching based on domain_id, and ? being whatever the value of domain_id is.

  3. #3
    SitePoint Enthusiast adear11's Avatar
    Join Date
    Oct 2003
    Location
    Richland MS
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats exactly what I needed. And it turns out I was pretty close to the answer.

    Thanks!!!

  4. #4
    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)
    chris, you have a non-aggregate in your SELECT clause (domain_name), therefore it needs to be in the GROUP BY clause as well

    also, i wouldn't use those parentheses, they are superfluous
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard chris_fuel's Avatar
    Join Date
    May 2006
    Location
    Ventura, CA
    Posts
    2,750
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    woops, goes to show what happens when you don't test out your queries before posting :/ This is one of those common mistakes I make that for some reason never clicks (another reason why I don't post in the Database Forums all that much..). As for the parens, I've always done them that way to make them stand out more for me. Though, some day other people will have to look at my code, so I suppose I should clean up my act in that area


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
  •