Nested join puzzler

[Eric]

I seem to have dug my way into a hole that's a bit over my head. I have
a db laid out as shown here and I can query it with some very basic
joins but I'm pretty at a loss on how to do the more complicated
queries. My main goal is to combine these 4 tables ( two partial
queries below ) into one query that will give me: all contacts for
each company within a given department. I know it's possible but I'm at
a loss as to how to put the pieces together.

Any help or suggestions would be gratly appreciated.

Thanks,
Eric

p.s. sorry about the formatting



mysql> select name, fname, lname from company right outer join contacts
on contacts.comp_id = company.comp_id order by name;
+-----------+---------+-------------+
| name | fname | lname |
+-----------+---------+-------------+
| comp_a | bob | lname_a |
| comp_a | mary | lname_b |
| comp_b | bill | lname_c |
| comp_c | mike | lname_d |
| comp_c | jane | lname_e |
| comp_d | helga | lname_f |
+-----------+---------+-------------+


mysql> select c.name, x.comp_id, d.name, d.dept_id from dept_company x,
company c, depts d where (c.comp_id = x.comp_id) and (d.dept_id =
x.dept_id);
+---------------+------------+----------+-----------+
| name | comp_id | name | dept_id |
+---------------+------------+----------+-----------+
| comp_a | 1 | dept_a | 1 |
| comp_b | 2 | dept_a | 1 |
| comp_c | 15 | dept_b | 2 |
+---------------+------------+----------+-----------+



Table structures

depts
----------
dept_id
name


dept_comp
-----------------
dept_id
comp_id


comapny
--------------
comp_id
name

contact
------------
cont_id
comp_id
fname
lname

[strawberry]

Eric wrote:

> I seem to have dug my way into a hole that's a bit over my head. I have
> a db laid out as shown here and I can query it with some very basic
> joins but I'm pretty at a loss on how to do the more complicated
> queries. My main goal is to combine these 4 tables ( two partial
> queries below ) into one query that will give me: all contacts for
> each company within a given department. I know it's possible but I'm at
> a loss as to how to put the pieces together.
>
> Any help or suggestions would be gratly appreciated.
>
> Thanks,
> Eric
>
> p.s. sorry about the formatting
>
>
>
> mysql> select name, fname, lname from company right outer join contacts
> on contacts.comp_id = company.comp_id order by name;
> +-----------+---------+-------------+
> | name | fname | lname |
> +-----------+---------+-------------+
> | comp_a | bob | lname_a |
> | comp_a | mary | lname_b |
> | comp_b | bill | lname_c |
> | comp_c | mike | lname_d |
> | comp_c | jane | lname_e |
> | comp_d | helga | lname_f |
> +-----------+---------+-------------+
>
>
> mysql> select c.name, x.comp_id, d.name, d.dept_id from dept_company x,
> company c, depts d where (c.comp_id = x.comp_id) and (d.dept_id =
> x.dept_id);
> +---------------+------------+----------+-----------+
> | name | comp_id | name | dept_id |
> +---------------+------------+----------+-----------+
> | comp_a | 1 | dept_a | 1 |
> | comp_b | 2 | dept_a | 1 |
> | comp_c | 15 | dept_b | 2 |
> +---------------+------------+----------+-----------+
>
>
>
> Table structures
>
> depts
> ----------
> dept_id
> name
>
>
> dept_comp
> -----------------
> dept_id
> comp_id
>
>
> comapny
> --------------
> comp_id
> name
>
> contact
> ------------
> cont_id
> comp_id
> fname
> lname

Not sure I understand the structure. Aren't departments usually WITHIN
companies - not the other way round? How can one know which department
bob lname_a works in - unless ALL contacts within a given company work
in the SAME department!

If you're trying to systematize the db so that every company has
departments that you're going to call 'Finance','Personnel' and
'Technical' for instance,then perhaps this should be explicitly
acknowledged in the contact information:

contact
______
contact_id
company_id
department_id
f_name
l_name

[Eric]

strawberry wrote:

> Eric wrote:

> > I seem to have dug my way into a hole that's a bit over my head. I have
> > a db laid out as shown here and I can query it with some very basic
> > joins but I'm pretty at a loss on how to do the more complicated
> > queries. My main goal is to combine these 4 tables ( two partial
> > queries below ) into one query that will give me: all contacts for
> > each company within a given department. I know it's possible but I'm at
> > a loss as to how to put the pieces together.
> >
> > Any help or suggestions would be gratly appreciated.
> >
> > Thanks,
> > Eric
> >
> > p.s. sorry about the formatting
> >
> >
> >
> > mysql> select name, fname, lname from company right outer join contacts
> > on contacts.comp_id = company.comp_id order by name;
> > +-----------+---------+-------------+
> > | name | fname | lname |
> > +-----------+---------+-------------+
> > | comp_a | bob | lname_a |
> > | comp_a | mary | lname_b |
> > | comp_b | bill | lname_c |
> > | comp_c | mike | lname_d |
> > | comp_c | jane | lname_e |
> > | comp_d | helga | lname_f |
> > +-----------+---------+-------------+
> >
> >
> > mysql> select c.name, x.comp_id, d.name, d.dept_id from dept_company x,
> > company c, depts d where (c.comp_id = x.comp_id) and (d.dept_id =
> > x.dept_id);
> > +---------------+------------+----------+-----------+
> > | name | comp_id | name | dept_id |
> > +---------------+------------+----------+-----------+
> > | comp_a | 1 | dept_a | 1 |
> > | comp_b | 2 | dept_a | 1 |
> > | comp_c | 15 | dept_b | 2 |
> > +---------------+------------+----------+-----------+
> >
> >
> >
> > Table structures
> >
> > depts
> > ----------
> > dept_id
> > name
> >
> >
> > dept_comp
> > -----------------
> > dept_id
> > comp_id
> >
> >
> > comapny
> > --------------
> > comp_id
> > name
> >
> > contact
> > ------------
> > cont_id
> > comp_id
> > fname
> > lname

>
> Not sure I understand the structure. Aren't departments usually WITHIN
> companies - not the other way round? How can one know which department
> bob lname_a works in - unless ALL contacts within a given company work
> in the SAME department!
>
> If you're trying to systematize the db so that every company has
> departments that you're going to call 'Finance','Personnel' and
> 'Technical' for instance,then perhaps this should be explicitly
> acknowledged in the contact information:

Hi Strawberry,

I see your point but I was taking a slightly different approach. This
database is intended to track vendors and contacts to our company. In
this case "dept" represents a class of service that a vendor provides,
such as: software, hardware, communications, networking etc.

The reason for the cross-reference table is because some vendors
provide services that apply to several "departments".


-Eric

>
> contact
> ______
> contact_id
> company_id
> department_id
> f_name
> l_name