SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Feb 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question mysql circular table query

    Hi,

    Can anyone help me create a query that will work the following:-

    I need to be able to look up a company AND their parent company from the following table. Only some companies have a parent company, others that do not have a parent have themselves listed as the parent.

    Table Structure is along the lines of

    CompanyID
    CompanyName
    ...
    CompanyParentID

    The CompanyParentID is linked to the CompanyID, and both are further linked to other tables in the database.

    Example data:-
    CompanyID | CompanyName | ... | CompanyParentID
    1 | ABC and Co | ... | 1
    2 | XYZ and Co | ... | 2
    3 | Alphabet Co | ... | 2
    ...

    Example results:

    CompanyName | ParentName
    ABC and Co | ABC and Co
    XYZ and Co | XYZ and Co
    Alphabet Co | XYZ and Co

    I have managed to learn my way around linking two or more tables, but not sure how to do this when the tables are in fact the same table.

    Thanks,

    Adam (slightly past newbie)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    this is a simple self-join
    Code:
    SELECT this.CompanyName 
         , parent.CompanyName AS ParentName
      FROM companies AS this
    LEFT OUTER
      JOIN companies AS parent
        ON parent.CompanyID = this.CompanyParentID
    i think it's a little unusual for a company to be its own parent

    more common is for a company that does not have a parent to have NULL in the CompanyParentID column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Feb 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Thank you for such a fast response.

    It is a unusual structure, it is set up because the ultimate parent gets billed for the works of child companies/clients, and obviously work done for the a top-level company still needs billing but to themselves!

    Adam

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    how many levels of subcompany are there?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Feb 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So far only 1 and its mainly used to cater for when a customer gives us work for their client but they pay, or when we do work for a secondary address or department.

    Have you a better way of doing things?

    Its important that someone can pull up a list of billable work whoever it is for, so that it gets invoiced on one invoice. it also contains a lot of other company information like addresses, telephone numbers, emails, contacts and my original idea was not to duplicate storage of this information.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i would set CompanyParentID to NULL if the company has no parent
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Feb 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks, I'll look at the likelihood of wanting to see any chain

    appreciate the input


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
  •