unusual form of join
I have 2 tables
Table 1 Name: filestable
Table 1 Fields:
id int
name varchar
deleted enum('y','n')
Table 2 Name: productstable
Table 2 Fields:
id int
name varchar
fileids text
deleted enum('y','n')
For each product fileids are stored as '1' or '1,2' i.e., if single file is associated with product, then only single file id will be displayed (as 开发者_高级运维'1'), but if multiple files are associated then file ids are separated with ',' (as '1,2')
How can I get output like:
fileid filename associated products (i.e. total product associated with the file)
Moreover, neither file nor product should have been deleted i.e., deleted = 'n'
How can I get above mentioned result?
SELECT f.id fileid,
f.name filename,
p.id productid,
p.name productname
FROM productstable p
INNER JOIN filestable f ON FIND_IN_SET(f.id, p.fileids)
AND f.deleted = 'n'
WHERE p.deleted = 'n'
But I strongly insist on rewriting this terrible schema to many-to-many relations.
精彩评论