MySQL如何选择具有不正确ID的记录? [英] MySQL How to select records with indrect IDs?

问题描述

我的表中有一列标记为primary_key和summary_id的列.每个记录的第二个字段summary_id中的值映射到另一条记录的primary_key字段.第三个字段template_id.我需要选择以下记录:

My table has a columns labeled primary_key and summary_id. The value in the second field summary_id in each record maps to the primary_key field of another record. There is a third field template_id. I need to select those records for which:

1. template_id是某个值.假设4.
2. primary_key至少匹配记录的summary_id字段之一.

1. template_id is a certain value. Let's say 4.
2. primary_key matches at least one of the records' summary_id field.

请不要告诉我重新设计桌子.我的下一个项目将有一个更好的设计，但我现在没有时间.我需要使用一个或多个查询来执行此操作；越少越好.理想情况下，有一种方法可以对一个查询执行此操作，但是如果需要更多查询，我可以.

Please don't tell me to redesign the tables. My next project will have a better design, but I don't have time for that now. I need to do this with one or more queries; the fewer the better. Ideally, there's some way to do this with one query, but I'm okay if it requires more.

这是我自己查询的结果. (我知道这很严重，这就是为什么我需要帮助.)

This is how far I've gotten with my own query. (I know it's seriously lacking, which is why I need help.)

SELECT DISTINCT esjp_content.template_id
FROM esjp_content
INNER JOIN esjp_hw_config ON esjp_content.template_id = esjp_hw_config.proc_id
INNER JOIN esjp_assets ON esjp_hw_config.primary_key = esjp_assets.hw_config_id
WHERE
    esjp_content.summary_id > 0
        AND
    (esjp_assets.asset_label='C001498500' OR esjp_assets.asset_label='H0065' OR esjp_assets.asset_label='L0009');

SELECT
    esjp_content.primary_key, esjp_content.template_id, esjp_content.content, esjp_content.summary_id
FROM
    esjp_content
WHERE
    esjp_content.template_id = 4;

我需要summary_id指向的记录.例如，如果summary_id为90，那么我需要primary_key为90的记录.

I need the records that summary_id points to. For example, if summary_id is 90, then I need the record where primary_key is 90.

推荐答案

您正在寻找至少存在一行的行，其中summary_id =您的主键.像这样.

You're looking for the existence of at least one row where summary_id = your primary key. like this.

SELECT *
FROM esjp_content c
WHERE template_id = 4
AND EXISTS (SELECT 1 FROM esjp_content c2 WHERE c2.summary_id = c.primary_key)

这篇关于MySQL如何选择具有不正确ID的记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！