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

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
pa开发者_运维知识库ge - 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 :\

---

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?

---

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