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
– ppeterka, Commented Dec 28, 2012 at 13:17
in a specific sql language dialect? is it only one or two values separate by a comma? — rene
– rene, Commented Dec 28, 2012 at 13:18
Is storing a delimited list in a database column really that bad? — eggyal
– eggyal, Commented Dec 28, 2012 at 13:22
It stores multiple values for location_id in table so its complicated — Durgesh Sonawane
– Durgesh Sonawane, Commented Dec 28, 2012 at 13:28

ppeterka · Accepted Answer · 2012-12-28 13:46:05Z

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))

Community · Accepted Answer · 2017-05-23 11:58:28Z

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, 2017 at 11:58

CommunityBot

11 silver badge

answered Dec 28, 2012 at 13:32

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

1 Comment

Muhammad Raheel Over a year ago

this return empty result set

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related