开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜