根据三个相应表中的数据从id表中删除行

Question

我有一个表'node‘，它有一个列'nid’。此id对应于其他三个表中的列'entity_id‘。我们称之为“pop”，“city”，“state”的三个表。“city”和“state”表分别有city_value和state_value列。

并非所有的“城市”和“州”表行在“pop”表中都有相应的“entity_id”。

从这里我可以得到两组数据。在pop表中有和entity_id的节点(setA)和没有的节点(setB)。

为了获得setA，我这样做：

SELECT nid FROM node
  LEFT OUTER JOIN pop
  ON node.nid = pop.entity_id
  WHERE pop.entity_id IS NOT null

我不知道如何从与setA具有相同城市和州的setB中删除'node‘中的行。

编辑:我的setA代码中有m.entity_id，但在WHERE行中应该是pop.entity_id。

编辑:我想我知道了。

SELECT DISTINCT setB.nid FROM 

(
    SELECT DISTINCT AA.nid, AA.title, BB.field_dialup_number_city_value, CC.field_dialup_number_state_value FROM 
    (   
        SELECT node.nid, node.title FROM node LEFT OUTER JOIN field_data_field_dialup_number_popcode pop  
        ON node.nid = pop.entity_id WHERE pop.entity_id IS NOT null  
    ) AA
    INNER JOIN field_data_field_dialup_number_city BB ON AA.nid = BB.entity_id 
    INNER JOIN field_data_field_dialup_number_state CC ON AA.nid = CC.entity_id
) setB


INNER JOIN 
(
    SELECT DISTINCT A.nid, A.title, B.field_dialup_number_city_value, C.field_dialup_number_state_value FROM 
    (   
        SELECT node.nid, node.title FROM node LEFT OUTER JOIN field_data_field_dialup_number_popcode pop  
        ON node.nid = pop.entity_id WHERE pop.entity_id IS null  
    ) A
    INNER JOIN field_data_field_dialup_number_city B ON A.nid = B.entity_id 
    INNER JOIN field_data_field_dialup_number_state C ON A.nid = C.entity_id
) setA


ON setA.field_dialup_number_state_value = setB.field_dialup_number_state_value

WHERE setA.field_dialup_number_city_value = setB.field_dialup_number_city_value

Answer 1

你说过setA是

SELECT nid FROM node  
LEFT OUTER JOIN pop  
ON node.nid = pop.entity_id  
WHERE m.entity_id IS NOT null  

setB将只是一个连接WHERE NULL

SELECT nid FROM node  
LEFT OUTER JOIN pop  
ON node.nid = pop.entity_id  
WHERE m.entity_id IS null  

如果您希望setA连接回城市和州，请尝试执行以下操作：

SELECT DISTINCT A.nid FROM
(
    SELECT nid FROM node LEFT OUTER JOIN pop  
    ON node.nid = pop.entity_id WHERE m.entity_id IS NOT null  
) A
INNER JOIN  city B ON A.nid = B.entity_id 
INNER JOIN state C ON A.nid = C.entity_id 
;

如果您希望setB连接回城市和州，请尝试执行以下操作：

SELECT DISTINCT A.nid FROM
(
    SELECT nid FROM node LEFT OUTER JOIN pop  
    ON node.nid = pop.entity_id WHERE m.entity_id IS null  
) A
INNER JOIN  city B ON A.nid = B.entity_id 
INNER JOIN state C ON A.nid = C.entity_id 
;