Reputation: 9782
This is second time that i can face this kind of issue to retrieve the data.
CREATE TABLE `pm_projects` (
`project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`project_name` varchar(255) NOT NULL,
`assigned_client` varchar(100) NOT NULL,
`project_detail` longtext NOT NULL,
`creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`project_status` tinyint(1) NOT NULL,
PRIMARY KEY (`project_id`),
KEY `assigned_client` (`assigned_client`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
On the above table i have a field assigned_client
that holds the multiple id's of the clients that are assigned to the project with comma separated(3,4,...
).
And i am trying to fetch the result on this table with the Assigned Client's Name(that is on my pm_users
table) with JOIN
, I tried the following:
SELECT
p.project_id, u.user_name, p.project_name,
p.creation_date, p.project_status
FROM pm_projects p
LEFT JOIN pm_users u ON u.user_id
IN (
'p.assigned_clients'
)
that returns the NULL
value of u.user_name
field.
Can i have to change my schema, if yes then how?
OR i am trying with wrong Query?
Upvotes: 0
Views: 94
Reputation: 166626
I would go with a many to many link approach.
Something like
CREATE TABLE pm_project_client_link(
project_id INT,
client_id INT
)
That would allow you to write the query something like
SELECT
p.project_id,
u.user_name,
p.project_name,
p.creation_date,
p.project_status
FROM pm_projects p INNER JOIN
pm_project_client_link pcl ON p.project_id = pcl.project_id INNER JOIN
pm_users u ON pcl.client_id = user_id
Upvotes: 1
Reputation: 1271241
You can use find_in_set
for this:
on find_in_set(u.user_id, p.assigned_clients) > 0;
Note that there are no single quotes around p.assigned_clients
. This is another error in your query (but even if you replaced it with back quotes, the query still wouldn't work).
However, the problem is your table schema. You should have a separate association table, with one row per user and assigned client.
Trying to store this all in one field will only lead to problems, overly-complicated queries, and performance problems in the future.
Upvotes: 2