1

I want to find all the houses does a user has based on the Roles, since roles values comma separated numbers I have used FIND_IN_SET and wrote a mysql query like as shown below, but it is showing wrong results

SELECT Name FROM Houses WHERE FIND_IN_SET(Roles, (SELECT r.Roles FROM Roles r WHERE users = 'Manu'))

enter image description here

My Expected reults based on users are as given below

enter image description here

Can anyone please help me on this

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Alex Man
  • 4,746
  • 17
  • 93
  • 178
  • Thinking. i will let you know – Yahya Mukhtar Mar 05 '18 at 15:47
  • `FIND_IN_SET` can only look for a single value in a comma-separated list, it can't be used to find the intersection of 2 comma-separated lists. – Barmar Mar 05 '18 at 15:55
  • 1
    Splitting up a comma-separated list is really hard in MySQL. You really need to normalize the schema, then it will be easy. – Barmar Mar 05 '18 at 15:55
  • @AamirR This isn't like that. He's not looking for one string being a substring of the other. He's trying to do an intersection of two comma-delimited lists. – Barmar Mar 05 '18 at 15:56
  • @Barmar oh sure these are commas, I thought dots :) – AamirR Mar 05 '18 at 15:59

1 Answers1

1

Normalize your schemas so there's a separate row for each row.

House_roles

House_name  Role
r_house_1   1
r_house_1   2
r_house_1   3
r_house_2   2
r_house_2   3
r_house_3   1
r_house_3   3
r_house_4   1
r_house_4   2

User_roles

User_name   Role
Manu        1
John        1
John        2
Sunny       3
Jack        1
Jack        2

Then the query is a join between these two tables:

SELECT House_name
FROM House_roles AS h
JOIN User_roles AS u ON h.role = u.role
WHERE u.User_name = 'Manu'

If you can't change the schema, see SQL split values to multiple rows for how to write a query to split the comma-delimited lists into rows. If a house or user can have up to 100 roles, create a table numbers that contains the numbers from 1 to 100 in a column named n. Then you can create temporary tables like this by joining your tables with the numbers table. Here's the query to create House_roles:

CREATE TEMPORARY TABLE House_roles (
    House_name VARCHAR(32),
    Role INT,
    INDEX(Role)
) AS
SELECT h.name, SUBSTRING_INDEX(SUBSTRING_INDEX(h.role, ',', n.n), ',', -1)
FROM House AS h
JOIN numbers AS n ON CHAR_LENGTH(h.role)
 -CHAR_LENGTH(REPLACE(h.role, ',', ''))>=n.n-1

It's similar for User_roles.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thanks for the suggestion, actually I cannot change the table schema since this is my client database, also we are having around lakhs of user using this application, so let say if we have 100000 users and if there are 100 roles, and if we are adding all the roles to 100000 user then we will get 10000000 entries for Roles table instead of 100000 (as per the current schema) . – Alex Man Mar 05 '18 at 16:08
  • Just because there are 100 roles, it doesn't mean that most users have all of them. Anyway, anything you do with comma-separated strings can't be indexed, so trying to match them will be so slow that it becomes unusable. – Barmar Mar 05 '18 at 16:12
  • I tried that substring but not getting exactly how to get my actual result, can you please help me on this – Alex Man Mar 05 '18 at 16:35
  • I've added the query. – Barmar Mar 05 '18 at 16:47
  • Did you create a table `numbers` with a column named `n`, containing the numbers from 1 to 100? – Barmar Mar 05 '18 at 19:12