How to write query for comparing column having comma-separated values?

Question

I am Using Joomla 2.5. I am using MYSQL database. I have a table job_field with the following columns:

cat_id  |  location_id
-----------------------
 1,4    |   66,70

I need to compare it with another table job_value

cat_id  |  location_id | name
--------------------------------
 1      |   70         | Atul
 4      |   70,80      | Amit
 4      |   80,66      | Amol
 1      |   66         | Pritam    
 3      |   70         | Rahul
 2      |   66,90      | Ajit
 1      |   74         | Raju
 4      |   65,22      | Manoj

I want output to compare cat_id and location_id columns from first table job_details with second table job_value columns cat_id and location_id.

And it will check each value from 1st table (job_details) that is location_id column value (66, 70) separately with 2nd table (job_value) location_id column. I will get output array as

  Array (
    1 70     Atul
    4 70,80  Amit
    4 80,66  Amol
    1 66     Pritam
 )

This is not a normalized structure. Basically: you're doomed - unless you reconsider the structure. — ppeterka, Dec 28 '12 at 13:17
in a specific sql language dialect? is it only one or two values separate by a comma? — rene, Dec 28 '12 at 13:18
[Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/a/3653574) — eggyal, Dec 28 '12 at 13:22
It stores multiple values for location_id in table so its complicated — Durgesh Sonawane, Dec 28 '12 at 13:28
I've read this question 5 times now and I still have no idea what joins with what and what you would like to be returned — Hugo Delsing, Dec 28 '12 at 13:33
It doesn't count in _which CMS_ you use a badly designed DB schema... So specifying Joomla 2.5 is completely irrelevant... — ppeterka, Dec 28 '12 at 13:43

ppeterka · Answer 1 · 2012-12-28T13:46:05.160

This is a _badbad^bad structure. Even if the problem can be solved, it shouldn't be solved. It will be slow, and unmaintainable.

Instead of the poor structure, something along these lines should be created for this part of the DB:

CREATE TABLE PERSON (
    person_id BIGINT,
    name VARCHAR(64),
    PRIMARY KEY (person_id)
);

CREATE TABLE LOCATION (
    location_id BIGINT,
    name VARCHAR(64),
    PRIMARY KEY (location_id)
);
CREATE TABLE CAT (
    cat_id BIGINT,
    name VARCHAR(64),
    PRIMARY KEY (cat_id)
);

CREATE TABLE CAT_LOCATION (
    cat_id BIGINT,
    location_id BIGINT,
    PRIMARY KEY (cat_id,location_id),
    FOREIGN KEY (cat_id) REFERENCES cat(cat_id),
    FOREIGN KEY (location_id) REFERENCES location(location_id)
);

CREATE TABLE CAT_LOCATION_PERSON (
    cat_id BIGINT,
    location_id BIGINT,
    person_id BIGINT,
    PRIMARY KEY (cat_id,location_id,person_id),
    FOREIGN KEY (cat_id) REFERENCES cat(cat_id),
    FOREIGN KEY (location_id) REFERENCES location(location_id),
    FOREIGN KEY (person_id) REFERENCES person(person_id)
);

And then it is easier than easy to get what you want by doing simple joins:

SELECT cl.cat_id, cl.location_id, p.name
FROM CAT_LOCATION cl 
JOIN CAT_LOCATION_PERSON clp on cl.cat_id = clp.cat_id and cl.location_id=clp.location_id
JOIN PERSON p on clp.person_id = p.person_id

(I refuse to write a query that would provide the output in the specified format, with numeric values separated by comma... (though it is possible through MySQL's GROUP_CONCAT functionality easily))

score 0 · Accepted Answer · edited May 23 '17 at 11:58

0

Try this ::

SELECT 
* 
FROM table1
JOIN table2  ON FIND_IN_SET(table1.location_id, table2.location_id) > 0

You can also refer Comma separated values in MySQL "IN" clause

edited May 23 '17 at 11:58

Community

1
1

answered Dec 28 '12 at 13:32

Sashi Kant

13,277
9
44
71

this return empty result set – Muhammad Raheel Dec 28 '12 at 13:34

How to write query for comparing column having comma-separated values?

2 Answers2