dongquechan4414
2010-01-14 11:41
浏览 31

SQL Query用于获取另一个表中不存在列中值的行

I have a products table with a column that contains a space separated list of ids (like: "23 42 365"), the column is called "categories". The numbers refer to rows ids in another table (categories).

I need to extract all product rows where all of the ids in the space separated list point to rows on the categories table that no longer exist.

I know this is not the best database design by a long way, however I have been presented with this task on an older system. I am not even sure it can be done entirely with an SQL statement, but because of the sheer number of records on the product table, it would be slower to use PHP logic to determine the rows to return. However, if thats the only way, thats what i'll do!

图片转代码服务由CSDN问答提供 功能建议

我有一个产品表,其中包含一个以空格分隔的ID列表的列(例如:“23 42 365” ),该列称为“类别”。 这些数字指的是另一个表(类别)中的行ID。

我需要提取所有产品行,其中空格分隔列表中的所有ID都指向类别表上的行 我知道这不是最好的数据库设计,但是我已经在旧系统上看到了这个任务。 我甚至不确定它是否可以完全使用SQL语句完成,但由于产品表上的记录数量很多,使用PHP逻辑来确定要返回的行会更慢。 但是,如果这是唯一的方法,那就是我要做的事情!

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dor2p0520 2010-01-14 11:47
    已采纳
    SELECT  m.*
    FROM    mytable m
    LEFT JOIN
            categories c
    ON      FIND_IN_SET(c.id, REPLACE(m.categories, ' ', ','))
    WHERE   c.id IS NULL
    
    打赏 评论
  • dongluni0568 2010-01-14 11:46

    You can use an outer join to get the missing rows, searching for where the category is null.

    http://dev.mysql.com/doc/refman/5.0/en/join.html

    打赏 评论

相关推荐 更多相似问题