CODEWITHSUNDEEP
phpmysqlsqljoomla2.5
Durgesh Sonawane
Durgesh Sonawane

Reputation: 95

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

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
 )

Upvotes: 2

Views: 1438

Answers (2)

ppeterka
ppeterka

Reputation: 20726

This is a badbadbad 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))

Upvotes: 3

Sashi Kant
Sashi Kant

Reputation: 13465

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

Upvotes: 0

Related Questions