Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Location
    NJ USA
    Posts
    21

    Unanswered: Query Optimize - Flatten Many to One query?

    I am currently looking for a more efficient way to write a query and any help or advice would be appreciated. I have a many to one relationship in my database and I need to write a query that creates a data set with the many to one table's data going across.

    Here is an example:

    Table1
    ID,Name,State
    AP1,Tom,NJ
    AP2,Jim,PA
    AP3,Sara,FL

    Table2
    ID,Table1ID,License,HasLicense
    1,AP1,A,Yes
    2,AP1,B,Yes
    3,AP1,C,No
    4,AP2,A,Yes
    5,AP2,B,No
    6,AP3,B,No
    7,AP3,E,No


    I need a query that will create:
    Table1ID,Name,State,LicenseA,LicenseB,LicenseC,Lic enseD,LicenseE
    AP1,Tom,NJ,Yes,Yes,No,,
    AP2,Jim,PA,Yes,No,,
    AP3,Sara,FL,,No,,No


    The only method I thought of was to create a temp. table for my final output and do a series of UPDATE statements for each License in the Many to One table.

    UPDATE Table_Final,Table2
    SET Table_Final.LicenseA = Table2.HasLicense
    WHERE Table_Final.Table1ID = Table2.Table1ID AND
    Table2.License = 'A';

    Doing this method in a table with millions of records took over 8 hours to complete. Is their a more efficient method for creating this output?


    Thank You in advance.
    J.D.
    Database Administrator

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    would GROUP_CONCAT work for you?

    also 8 hours is too long. Do you have your tables properly indexed? what about database optimization, have you tuned that properly?

    even millions of records is not that large for mysql to handle.

  3. #3
    Join Date
    May 2004
    Location
    NJ USA
    Posts
    21
    Thanks but I don't think the Group_Concat would work for this purpose. Great idea but the result must have the same format throughout the file separated as different fields.

    If I'm not mistaken, using Group_Concat would order the licenses, not put them in a particular column.

    In the example given, I'm thinking the result of Group_Concat would give:

    Table1ID,Name,State,LicenseA,LicenseB,LicenseC,Lic enseD,LicenseE
    AP1,Tom,NJ,No,Yes,Yes
    AP2,Jim,PA,No,Yes
    AP3,Sara,FL,No,No

    Instead of
    Table1ID,Name,State,LicenseA,LicenseB,LicenseC,Lic enseD,LicenseE
    AP1,Tom,NJ,Yes,Yes,No,,
    AP2,Jim,PA,Yes,No,,
    AP3,Sara,FL,,No,,No

    If it helps, users will download this report into an Excel CSV document with each of these licenses in a separate field.

    And I do agree that 8 hours is too long which was probably an over-estimate anyway but that's the total run-time of the script. Last week the script took a total of 4 hours to run though. It's just that each update takes 5-10 minutes on average and their is an update for each of the many licenses. I'm hoping their is a more efficient or better way of running this report instead of running several update statements on a temp. table and then exporting the temp. table.

    Thanks Again.
    Last edited by J.D.; 05-24-07 at 16:58.
    J.D.
    Database Administrator

  4. #4
    Join Date
    May 2007
    Posts
    4
    Hi,
    if you just want to list out the details you can work with self inner joins on the tables .. something like this

    select table1.id, name, state, a.haslicense LA, b.haslicense LB, c.haslicense LC, d.haslicense LD, e.haslicense LE
    from
    Table1 left outer join Table2 a on (Table1.ID=a.Table1ID and a.license='A')
    left outer join Table2 b on (Table1.ID=b.Table1ID and b.license='B')
    left outer join Table2 c on (Table1.ID=c.Table1ID and c.license='C')
    left outer join Table2 d on (Table1.ID=d.Table1ID and d.license='D')
    left outer join Table2 e on (Table1.ID=e.Table1ID and e.license='E')

    I haven't tried out the query but therotically it should do the trick

  5. #5
    Join Date
    May 2004
    Location
    NJ USA
    Posts
    21
    That is exactly what I was looking for. I tested it on a small sub-set and it worked exactly how I needed.

    Thank you.
    J.D.
    Database Administrator

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Look just like a pivot table to me...

Posting Permissions

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