Professional Web Applications Themes

help with complex query - MySQL

Hi, I'm having trouble with a complex query and was hoping to get some help. Here are my tables: Company ---------------- company_id | company_name | company_phone | company_county -------------------------------------------------------------------------------------------------- 1 | Joe's Garage | 555-1212 | Orange 2 | Sallys Shells | 555-1212 | Green 3 | Peters Pickles | 555-1212 | Orange ------------------------------------------------------------------------------------------------- Salesman -------------- salesman_id | salesman_name | regions ----------------------------------------------------------------------- 1 | Jane | 5 2 | Joey | 3 ----------------------------------------------------------------------- Regions ------------ region_id | county_name ----------------------------------------------------------------------- 11 | Orange 42 | Green 53 | Purple ----------------------------------------------------------------------- Regions_lookup ------------------------- salesman_id | region_id ----------------------------------------------------------------------- 1 | 11 1 | ...

  1. #1

    Default help with complex query

    Hi,

    I'm having trouble with a complex query and was hoping to get some
    help.

    Here are my tables:

    Company
    ----------------
    company_id | company_name | company_phone | company_county
    --------------------------------------------------------------------------------------------------
    1 | Joe's Garage | 555-1212 | Orange
    2 | Sallys Shells | 555-1212 | Green
    3 | Peters Pickles | 555-1212 | Orange
    -------------------------------------------------------------------------------------------------

    Salesman
    --------------
    salesman_id | salesman_name | regions
    -----------------------------------------------------------------------
    1 | Jane | 5
    2 | Joey | 3
    -----------------------------------------------------------------------


    Regions
    ------------
    region_id | county_name
    -----------------------------------------------------------------------
    11 | Orange
    42 | Green
    53 | Purple
    -----------------------------------------------------------------------


    Regions_lookup
    -------------------------
    salesman_id | region_id
    -----------------------------------------------------------------------
    1 | 11
    1 | 53
    2 | 11
    2 | 42
    -----------------------------------------------------------------------

    Each salesman has to call some companies in their assigned regions
    (counties). The assigned regions are stored in the Regions_lookup
    table. I want to list out all companies in a given salesman's
    region. What would be the MySQL query?

    cas Guest

  2. #2

    Default Re: help with complex query

    cas wrote: 

    Untried, but something like:

    SELECT company_name FROM Company
    JOIN Regions on Company.company_county = Regions.county_name
    JOIN Regions_lookup on Regions.region_id = Regions_lookup.region_id
    JOIN Salesman on Regions_lookup.salesman_id = Salesman.salesman_id
    WHERE Salesman.salesman_name = 'Jane';

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: help with complex query

     
    > [/ref]
     

    Yes, that worked like a charm! Thanks so much. In the past I would
    have programmed it with some bersome looping and several database
    queries. I am trying to learn how to code more efficiently.
    Thanks again!
    Carol

    cas Guest

Similar Threads

  1. Need help with complex query
    By Jatrix in forum Coldfusion Database Access
    Replies: 8
    Last Post: June 13th, 06:51 PM
  2. Help! Complex query
    By RuBot in forum Coldfusion Database Access
    Replies: 5
    Last Post: March 7th, 12:47 AM
  3. Help for quite complex query.
    By _andrea.l in forum MySQL
    Replies: 3
    Last Post: January 1st, 04:22 PM
  4. Help with complex query
    By Mike in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 17th, 08:19 AM
  5. A complex query
    By Venkatesan M in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 1st, 02:27 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139