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.

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.

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.

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

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.

Look just like a pivot table to me...