Query Help~!!!!

kalons · #1 (**permalink**) 12-16-10, 04:58

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!

it-iss.com · #2 (**permalink**) 12-16-10, 06:49

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.

r937 · #3 (**permalink**) 12-16-10, 08:44

Quote:

Originally Posted by it-iss.com

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

it-iss.com · #4 (**permalink**) 12-16-10, 09:49

Nice function FIND_IN_SET. Didn't know it existed and you are right about the overall performance too.

Internet Services

Web Development

Digital Marketing

Consumer Tech