php - Select from two tables where rows from second table should be shown as columns in first -

i have 2 tables in mysql:

table entry:

id | name         | date 1  | test entry   | 12/12/2013 2  | test entry 2 | 12/12/2013 

table note

id | entry_id | name  | value  1  | 1        | note1 | value1 2  | 1        | note2 | value2 3  | 2        | note1 | value1 4  | 3        | note4 | value4 

where entry_id in note foreign key id in entry.

is there solution can create select give me result following?

entry_id | name         | note1   | note2  | note3  1        | test entry   | value1  | value2 |   - 2        | test entry 2 | value 1 |    -   | value3 

i want avoid left join here (current implementation working this) , want join note once if possible. left join not here, because not know how many notes can attached 1 entry. current implementation works way first fetch distinct notes name can found in note, , build select foreach through php. finally, select statement looks this:

select  e.id entry_id, e.name name, n1.value note1_value, n2.value note2_value, n3.value note3_value entry e join left note n1 on e.id = n1.entry_id , n1.name = 'note1' join left note n2 on e.id = n2.entry_id , n2.name = 'note2' join left note n3 on e.id = n3.entry_id , n3.name = 'note3' 

things tricky when join on note 20-30 times.

no, there not way without joins.

i recommend doing 2 queries.

1. select * entry id = id
2. select * note entry_id = id

and join results in application code. you're right, left joins going bad.