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 wrote:
> 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?
>

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.
jstucklex@attglobal.net
==================

> > I'm having trouble with a complex query and was hoping to get some
> > help.
>
> > Here are my tables: <snip>

> 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';

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