CODEWITHSUNDEEP
mysqlmysql-python
Rao
Rao

Reputation: 2972

Need help in Complicated MySQL Query

I am developing an application in Python, PyQt4 and MySQL.

I have a table with employee details (id,empID,name,permissions) permissions will contains comma separated data with project_empID under whom the employee is working.

Sample table is posted at SQLFIDDLE.

I need a query to retrieve data as below

empId   Name    Superior
1863     Rao     NULL
P4557    Kuri    NULL
P4645    Deep    Rao - 1863
P6682    Sha     Kuri - P4557,Rao - 1863
P9645    Krish   Kuri - P4557
P8516    Sati    Rao - 1863
P4568    Suri    Rao - 1863

I Know that we can get it with SUBSTRING_INDEX,GROUP_CONCAT but unable to proceed further. I am not getting an idea on how to proceed further to get the required result in single SQL

Upvotes: 0

Views: 59

Answers (2)

Noam Rathaus
Noam Rathaus

Reputation: 5608

Because your data is a bit inconsistent and mixes both strings and IDs, it makes life hard, this is the "simpliest" solution I could work out:

SELECT ChildID, Child, GROUP_CONCAT(IF(Matched > 0, CONCAT(empName, ' - ', empID), NULL))
FROM
(SELECT emp.empID, emp.empName, empcross.empID as `ChildID`, empcross.empName AS `Child`, 
       COALESCE(FIND_IN_SET(emp.empID, 
                    REPLACE(REPLACE(REPLACE(REPLACE(empcross.permissions, 'pr_', ''), 'rr_', ''), 'is_', ''), 'ir_', '')
                   ), FIND_IN_SET(emp.empID, 
                    REPLACE(REPLACE(REPLACE(REPLACE(empcross.permissions, 'pr_', ''), 'rr_', ''), 'is_', ''), 'ir_', '')
                   ), 0) AS `Matched`
FROM emp
LEFT JOIN emp as empcross ON emp.empID != empcross.empID) AS `Cross`
GROUP BY ChildID

NOTE I had to clean out the permissions to make it work with FIND_IN_SET, if you have REGEX REPLACE support in your MySQL I suggest using it for cleaner code.

Upvotes: 2

mtt
mtt

Reputation: 106

This table is not well designed, it's strongly not recommended to put multiple values in one field. It will decrease database speed and you'll have hard to maintain, unnecessary complex queries.

You should read a bit about Database normalization.

The sample solution on multiple values issue:

PermissionId    empId    SuperiorId
1    1863     NULL
2    P4557    NULL
3    P4645    1863
4    P6682    P4557
5    P6682    1863

As you can see, P6682 has two records, one per one superior. Then you can use JOIN to retrieve information you need.

Upvotes: 1

Related Questions