sql join tables where 1 column has comma

by

You should really look at Database normalization and first normalize your structure by adding a junction table and holds a relation from tablec each relation stored in tablec will be stored in new junction table but not as comma separated list each row will hold id of c and one user id per row ,if you can’t alter your schema you can use find_in_set to find values in set

select *  
from  tblC c
JOIN tblB b
ON (find_in_set(b.userid,c.userids) > 0)
where c.nname="new1"

See demo

Edit for normalize schema

I have removed userids column from your tblC and instead i have created a new junction table as tblC_user with 2 columns c_id this will related to the id column of tblC and second one userid to store user relations users for tblC see sample schema for tblC

CREATE TABLE if not exists tblC
(
id int(11) NOT NULL auto_increment ,
nname varchar(255),
 PRIMARY KEY (id)
);

INSERT INTO tblC (id, nname) VALUES
('1', 'new1'),
('2', 'new2'),
('3', 'new3'),
('4', 'new4'),
('5', 'new5');

And here is your junction table as tblC_user

CREATE TABLE if not exists tblC_user
(
 c_id int,
 userid int
);

INSERT INTO tblC_user (c_id,userid) VALUES
('1','1'),
('1','2'),
('2','1'),
('2','3'),
('3','1'),
('3','4'),
('4','3'),
('4','2'),
('5','5'),
('5','2');

In above if you notice i haven’t stored any comma separated relations each relation of user for tblC is stored in new row ,for you concerned result set i have used junction table in join also new query will be like below

select *  
from  tblC c
join tblC_user cu on(c.id = cu.c_id)
join tblB b on (b.userid = cu.userid)
where c.nname="new1"

Demo 2

Now above query can can be optimized by using indexes you can maintain cascading relations easily

More Related Contents:

  1. Recreating a query using INNER JOIN? [duplicate]
  2. Create a Cumulative Sum Column in MySQL
  3. Equivalent of explode() to work with strings in MySQL
  4. How can I tell when a MySQL table was last updated?
  5. MySQL “WITH” clause
  6. mysql select dynamic row values as column names, another column as value
  7. Operand Should Contain 1 Column – MySQL NOT IN
  8. MySQL DELETE FROM with subquery as condition
  9. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  10. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  11. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  12. Simple Random Samples from a Sql database
  13. MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB
  14. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  15. How do I use properly CASE..WHEN in MySQL
  16. Determine Rank based on Multiple Columns in MySQL
  17. Checking multiple columns for one value
  18. What is better in MYSQL count(*) or count(1)?
  19. Delete query not working in mysql
  20. How to insert data to MySQL with auto-incremented column(field)?
  21. How to get the count of each distinct value in a column? [duplicate]
  22. How to enable INNODB in mysql
  23. Bash script to insert values in MySQL
  24. How to get current date & time in MySQL?
  25. How do I count columns of a table
  26. Laravel join with 3 Tables
  27. How to regex in a MySQL query
  28. MySQL 5.6 – DENSE_RANK like functionality without Order By
  29. Syntax error on MERGE statement
  30. ORDER BY alphabet first then follow by number

Leave a Comment