Professional Web Applications Themes

Nested join puzzler - MySQL

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 ...

  1. #1

    Default Nested join puzzler

    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

    Eric Guest

  2. #2

    Default Re: Nested join puzzler


    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

    strawberry Guest

  3. #3

    Default Re: Nested join puzzler


    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
    Eric Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. SQL join
    By sikhar in forum Coldfusion Database Access
    Replies: 15
    Last Post: April 20th, 04:21 PM
  3. Union or Join or Nested Select - Can't Remember
    By Knum in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 14th, 11:48 PM
  4. Checkbox column puzzler.
    By jake in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: January 3rd, 10:09 PM
  5. Baffled? This IZ a REAL PUZZLER!!
    By Ellen Morrison in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 16
    Last Post: August 29th, 12:07 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