Select rows from table where id is in a comma seperated list in another table

Question

i have these two queries, that i want to join together, but am not skilled enough to do it.

can anyone help me ?

this is selecting the row that matches the search which contains a column cids, that in turn i need to use in the second query

SELECT cids,tp FROM searches_done where (keyword='keyword' and city='city' and page='page' and ipp='ipp') LIMIT 1

this query selects the actual result rows, which comparing the id of each company to the column seperated values we got in the first query (cids) also, it needs to attached the column tp from the first query to each result

SELECT c.*,s.tp FROM companies c WHERE c.id in (cids)

currently, it works since i run two queries ( by code ), and use the results i get from the first query in the second query.

searches_done table structure:

id - autoincrement unique int  
keyword - varchar(255) - keyword that was searched
page - int - paging page ( i.e: page=0 its the first page etc.. )
city - varchar(255) - keyword for city that was searched 
cids - text - comma seperated values of the companies ids 
                ( that were found matching this search )   
tp - int - total pages found
ipp - int - itemsperpage ( a search could be done with only 10 items per page
                         , so diffrent results might be found with 10/100 )

i am now working on creating a new table that will contains sd_id, c_id to normalize the database :\

so how should i save them ? creating another table for those values seems weird. or am i wrong? — Rafael Herscovici, Jul 29 '11 at 09:37
We need more info on the table structure, but yes that's usually the solution. — Jacob, Jul 29 '11 at 09:41
There is no more.. all the fields in my tables are as shown. Companies table contains more fields ( company data, like phone etc.. ) but has no connection to other tables ( beside the ID , that as shown above is also written in searches_done.cids) — Rafael Herscovici, Jul 29 '11 at 09:46
Ok, could you edit the question to describe your searches_done table? — Jacob, Jul 29 '11 at 09:55
OK, now i have a table named cids that contains sd_id,c_id. is that any better? — Rafael Herscovici, Jul 29 '11 at 10:08

score 4 · Answer 1 · edited May 23 '17 at 12:26

4

Function FIND_IN_SET() can be used:

SELECT ...
FROM companies c
  JOIN searches_done sd
    ON FIND_IN_SET( c.id, sd.cids) > 0
WHERE sd.keyword = 'keyword' 
  AND sd.city = 'city'
  AND sd.page = 'page'
  AND sd.ipp = 'ipp'

But you should really normalize the table. There are several benefits from not using comma-separated lists.

How to transfer data into a normalized association table:

--- create table
CREATE TABLE search_companies
( sd_id INT NOT NULL
, c_id INT NOT NULL
, PRIMARY KEY (sd_id, c_id)
, FOREIGN KEY sd_id REFERENCES searches_done(id)    --- if you use InnoDB
, FOREIGN KEY c_id REFERENCES companies(id)         --- 
) ;

--- transfer data
INSERT INTO search_companies
    (sd_id, c_id)
SELECT DISTINCT
    sd.id, c.id
FROM companies c
  JOIN searches_done sd
    ON FIND_IN_SET( c.id, sd.cids) > 0

Then your query would be using simple JOINs:

SELECT ...
FROM companies c
  JOIN searches_companies sc
    ON sc.c_id = c.id
  JOIN searches_done sd
    ON sd.id = sc.sd_id 
WHERE sd.keyword = 'keyword' 
  AND sd.city = 'city'
  AND sd.page = 'page'
  AND sd.ipp = 'ipp'

After checking that everything is fine and all queries, (SELECTs, INSERTs, DELETEs, UPDATEs) run fine, you can drop the cids field.

There's a nice answer here, regarding the question: is storing a comma separated list in a database column really that bad?

edited May 23 '17 at 12:26

Community

1
1

answered Jul 29 '11 at 10:10

ypercubeᵀᴹ

113,259
19
174
235

You always seem to come up with a solution! ;) +1 – Jacob Jul 29 '11 at 10:17
Question: does the `JOIN` and `FIND_IN_SET' is faster or prefered over `WHERE c.id in (SELECT cids FROM searched_done) ? – Rafael Herscovici Jul 29 '11 at 10:22
@Dementic: JOIN is usually the fastest choice. `WHERE c.id IN (2,5,17,42,...)` is usually fast too. (Both with proper indexing). `FIND_IN_SET()` is the slowest of all as the whole table (`c.id` from table `companies` in this case) has to be scanned, no index can be used. – ypercubeᵀᴹ Jul 29 '11 at 10:31
1

The problem with comma-separated lists is that (although `WHERE c.id IN (2,5,17,42,...)` can be used and be fast), you have to run two queries for one specific search_done record. More important, you can't enforce referential integrity (for example, how you delete c.id from all lists when a company is deleted?, how you check that no c.id is added twice in a list?), you can't easily (or fast) count how many items exist in the (comma-separated) list or do other aggregate calculations. – ypercubeᵀᴹ Jul 29 '11 at 10:35
@Dementic: check the link I added in the end of the answer. – ypercubeᵀᴹ Jul 29 '11 at 11:02

score 1 · Accepted Answer · answered Jul 29 '11 at 10:12

You can almost leave the searches_done table as it is:

searches_done table structure:

id - autoincrement unique int
keyword - varchar(255) 
page - int 
city - varchar(255) 
tp - int 
ipp - int

Then add another table:

searches_company:

search_id int foreign key searches_done.id
company_id int foreign key company.id

You can then join the two tables to get your results:

SELECT c.id, tp 
FROM companies c
INNER JOIN searches_company sc ON c.id = sc.company_id
INNER JOIN searches_done sd ON sc.search_id = sd.id
WHERE ...

Select rows from table where id is in a comma seperated list in another table

2 Answers2