0

I basically would like to merge details from the details table in the row of the master. So let's say I have a master table called tbMaster and a detail table called tbDetail with the following structures:

tbMaster
---------
ID Name
1  John
2  Sam
3  Bob
....


tbDetail
----------------------------------
ID IDmaster detailtype detailvalue
1  1        age        25
2  1        address    Longview Drive 48
3  1        wifename   Mary
4  2        age        31
5  2        address    MI, Courtesy Road 21
6  2        wifename   Rhonda
....

I did not build the DB so it's not my design... But now I need to show a dataset where for each master record, I will have extra fields obtained from the detail table like bellow:

SQL result:

ID name age  wifename  address
--------------------------------------------
1  John 25   Mary      Longview Drive 48
2  Sam  31   Rhonda    MI, Courtesy Road 21

How can I achieve this? I actually have more than one detail table for the same master with various data inside, so I will need multiple details from multiple detail tables. I guess if I figure out how to do it for one detail table, I can solve the rest of the query.

So far I have no idea where to start... Is this even possible with simple SQL?

user1137313
  • 2,390
  • 9
  • 44
  • 91
  • are the distinct detailtypes fixed? – Vamsi Prabhala Apr 26 '16 at 16:46
  • 1
    that's a pivot query, and mysql doesn't support them directly. the workarounds get ugly, fast. you'd be better off doing the column->row conversion in client-side code. – Marc B Apr 26 '16 at 16:49
  • `SELECT M.ID, M.Name, MAX(IF(D.detailtype='age',detailValue,NULL)) as age, MAX(IF(D.detailtype='wifename',detailValue,NULL)) as wifename, MAX(IF(D.detailtype='address',detailValue,NULL)) as address FROM tbMaster M INNER JOIN tbDetail D ON M.ID = D.IDMaster GROUP BY M.ID,M.Name` http://sqlfiddle.com/#!9/02a0b/1 – Tin Tran Apr 26 '16 at 17:03
  • The distinct detailtypes are FIXED. Each master record has the same type of details (same number of details and types of details) – user1137313 Apr 26 '16 at 17:27
  • Great stuff @Tin Tran make it an answer, since your code works. Thank you – user1137313 Apr 26 '16 at 17:28
  • glad it worked out for ya, i can't make an answer because it's marked as duplicate – Tin Tran Apr 26 '16 at 17:29

0 Answers0