mysql - Collating data from two tables -

i'm using following statement try , collect , display data correctly. necessary to 'left join' 1 table collect more information, should it's not necessary second case (but such work-around).

select     coalesce(building.campus_id, campus.campus_id) campus      member.* location left join cu_member member on     (member.member_id = location.member_id) left join cu_building building on     (location.params 'building_id=%' = building.id)  left join cu_campus campus on     (location.params 'campus_id=%' = campus.id) 

i'm above query, want use wildcard value.

left join cu_building building on     ('39' = building.id)  

below how location table looks. i'm trying use data params column resulting campus table (building). need fields containing building_id tag, not 'campus_id`, because known.

-----------------------------  member_id  | params   -----------------------------  1          | building_id=39  2          | building_id=24  3          | campus_id=6  4          | campus_id=3  5          | building_id=11  6          | campus_id=14  7          | building_id=15 

this how building table looks. lists building part of campus.

--------------------------  building_id  | campus_id  --------------------------  39           | 5  24           | 4  11           | 2  15           | 2 

i have table named `campus'. problem is, table lists, hoping use table in order display correct data in final result.

--------------------------  campus_id   | name  --------------------------  6           | ...  3           | ..  14          | . 

the result want achieve mysql query this. here, collected results shown in 1 table.

-----------------------------  member_id  | campus   -----------------------------  1          | 5                      (building_id=39)  2          | 4                      (building_id=24)  3          | 6                      (campus_id=6)  4          | 3                      (campus_id=3)  5          | 2                      (building_id=11)  6          | 14                     (campus_id=14)  7          | 2                      (building_id=15) 

first things first. you need revise database structure. location table give infinite number of problems, complicating each , every query ever need join members buildings , campuses. per data shown, should have building_id , campus_id on member table. another, softer, solution have building_id , campus_id column in location table.

that said, not need neither regex, nor likes have query work. should work adeguately:

select   coalesce(b.campus_id, c.id) campus    m.*      cu_member   m join      location    l on l.member_id = m.member_id left join cu_building b on l.params = concat('building_id=',b.id) left join cu_campus   c on l.params = concat('campus_id=',c.id) 

i can see last join seems bit redundant, since need campus id, , not name or other info. resides in location table, seem unnecessary join campus table. problem is embedded in params column. extracting mess.