Results 1 to 4 of 4

Thread: Query Help~!!!!

  1. #1
    Join Date
    Dec 2010
    Posts
    1

    Red face Unanswered: Query Help~!!!!

    Hi guys, thanks in advance to see my topic.
    Here is my problem:

    There is two tables named Factory Table & Office Table,and see below:

    Factory Table
    FID --- OID
    1 ------ 1,2
    2 ------ 3
    3 ------ 2,4,5
    4 ---- --- 5


    Office Table
    OID --- OfficeName
    1 ----- - -- A
    2 ------ --- B
    3 ------ --- C
    4 --- -- -- D
    5 --- ------ E


    What I want to output
    FID ------- Name
    1 ------ -- A,B
    2 ------ -- C
    3 --- --- -- B,D,E
    4 ------ -- E


    Please help~! Thank you so much!

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Can you confirm that in Factory table you have OID as a comma separated value of the identifiers of the offices ID?

    If that is the case then writing a single query might be difficult. If on the other hand you have multiple entries:

    Factory Table
    FID --- OID
    1 ------ 1
    1 ------ 2
    2 ------ 3
    3 ------ 2
    3 ------ 4
    3 ------ 5
    4 ------ 5

    Having this type of setup will simplify your SQL statement. Have a look at GROUP_CONCAT function to give you the results in the comma separated way for your query.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by it-iss.com View Post
    ...writing a single query might be difficult.
    oh, it's not all that difficult, it's just slow as cold glue because it requires a table scan
    Code:
    SELECT f.fid
         , GROUP_CONCAT(o.officename) AS offices
      FROM factory AS f
    INNER
      JOIN office AS o
        ON FIND_IN_SET(o.oid,f.oid)
    GROUP
        BY f.fid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Nice function FIND_IN_SET. Didn't know it existed and you are right about the overall performance too.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •