Trouble using GROUP_CONCAT and joins

duckduckgoose

I'm trying to figure out how to use joins with a group concat.

My profile_values table has entries that look like this:

uid | fid | value
1 | 1 | Moe
1 | 2 | Miller
1 | 3 | S
1 | 4 | 879

2 | 1 | Moe
2 | 2 | Miller
2 | 3 | S
2 | 4 | 323

3 | 1 | John
3 | 2 | Doe
3 | 3 | S
3 | 4 | 844

4 | 1 | Jane
4 | 2 | Smith
4 | 3 | S
4 | 4 | 866

5 | 1 | John
5 | 2 | Doe
5 | 3 | S
5 | 4 | 332

I'm to pull only the distinct, concatenated first and lastname values, but don't know how to integrate the group_concat function into the overall query, which is a series of joins to other tables...

SELECT og.uid, pv.value from c_type_S s 
LEFT JOIN c_f_S cf on s.nid = cf.field_S_nid 
LEFT JOIN c_type_CH cc on cf.nid = cc.nid 
LEFT JOIN og_uid og on og.nid = cc.nid 
LEFT JOIN profile_values pv on og.uid = pv.uid where s.nid = 22 
AND og.uid IS NOT NULL AND og.nid = 570 
AND LOWER(cc.field_CH_type_value) = 'S' 
AND pv.fid = 2 
ORDER BY og.nid, pv.value

Bjom

since it is not clear to me how that query relates to the shown table, nor how the given table (that are all data from one table, right) is populated/structured....it is hard to say what you need to do...

if it is this one table and you want to put together last and first name then this query would achieve it:

SELECT DISTINCT CONCAT(tA.value, ' ', tB.value) AS fullName 
    FROM (SELECT uid, value FROM profile_values WHERE fid = 2) AS tA 
        INNER JOIN (SELECT uid, value FROM profile_values WHERE fid = 1) AS tB 
            ON tA.uid = tB.uid;

(hopefully i did not produce a syntax error 😉 )

Bjom