N_E N_E - 6 months ago 22
SQL Question

MySQL Join on LIKE statement

I need to count how many users are in each group in a database. Unfortunately the database design is not great and the users uids are stored against the group in the group table in a LONGTEXT field column name owncloudusers.
Example of owncloudusers data :


{i:0;s:36:"25C967BD-AF78-4671-88DC-FAD935FF1B26";i:1;s:36:"40D6866B-EA06-4F39-B509-8CE551CC1924";i:2;s:36:"7724C600-DE23-45C8-8BFD-326B0138E029";i:3;s:36:"D6FF37EC-11F4-471F-94C9-F3A28416CF1F";i:4;s:36:"F70C6D03-B7BA-44E4-B703-9AF3EED9BC03";}


I thought I could use a query with a LIKE on the join to compare the user's uid and look inside owncloudusers and see if there is a match.

The closest I have got is:

SELECT T1.owncloudname, count(T2.owncloud_name) AS Users
FROM oc_ldap_group_members T1
LEFT JOIN oc_ldap_user_mapping T2 ON T1.owncloudusers LIKE('%:"'||T2.owncloud_name||'";%')
GROUP BY owncloudname;


T1 table holds the groupings and who is tagged to that group
T2 table holds the users data. column owncloud_name is the users uid column

I have tried a few approaches I found on stackoverflow CONCAT on the LIKE join and LIKE('%:"'+T2.owncloud_name+'";%')

But no joy. The current statement I have returns 0 users against all the groups but I know this is not right.

I know it much but an issue on the join not sure where to go with it next.

Any assistance would be much appreciated.

Answer

I think you need a simple

    SELECT T1.owncloudname, count(*) AS Users
    FROM oc_ldap_group_members T1
    LEFT JOIN oc_ldap_user_mapping T2 ON T1.owncloudusers LIKE '%T2.owncloud_name%' 
    GROUP BY owncloudname;

If you need concat try

    SELECT T1.owncloudname, count(T2.owncloud_name) AS Users
    FROM oc_ldap_group_members T1
    LEFT JOIN oc_ldap_user_mapping T2 ON T1.owncloudusers 
       LIKE concat( '%',T2.owncloud_name,'%' )
    GROUP BY owncloudname;