php - 从另一个表中不存在的表中选择mysql php

Question

SQL FIDDLE

我只想从几个表中选择，如果它们不存在于另一个表中，我的选择查询是这样的

SELECT *
,t1.pin AS table1_pin
,t3.pin AS table2_pin
,t6.pin AS table3_pin
,t9.pin AS table4_pin
,t2.tin AS table1_tin
,t2.first_name AS table1_firstname
,t2.last_name AS table1_lastname
,t2.middle_name AS table1_middlename
,t2.suffix AS table1_suffix
,t5.tin AS table2_tin
,t5.first_name AS table2_firstname
,t5.last_name AS table2_lastname
,t5.middle_name AS table2_middlename
,t5.suffix AS table2_suffix
,t8.tin AS table3_tin
,t8.first_name AS table3_firstname
,t8.last_name AS table3_lastname
,t8.middle_name AS table3_middlename
,t8.suffix AS table3_suffix
,t10.tin AS table4_tin
,t10.first_name AS table4_firstname
,t10.last_name AS table4_lastname
,t10.middle_name AS table4_middlename
,t10.suffix AS table4_suffix
,t1.effectivity_qtr AS table1qtr
,t1.effectivity_year AS table1year
,t4.effectivity_qtr AS table2qtr
,t4.effectivity_year AS table2year
,t7.effectivity_qtr AS table3qtr
,t7.effectivity_year AS table3year
,t9.effectivity_qtr AS table4qtr
,t9.effectivity_year AS table4year 
FROM 
table1 AS t1 
LEFT JOIN table1_ AS t2 ON t1.pin = t2.pin AND t1.status = t2.status 
LEFT JOIN table2 AS t3 ON t1.pin= t3.table2_pin AND t1.status = t3.status 
LEFT JOIN table2_ AS t4 ON t3.pin = t4.pin AND t3.status = t4.status 
LEFT JOIN table2__ AS t5 ON t3.pin = t5.pin AND t3.status = t5.status 
LEFT JOIN table3 AS t6 ON t1.pin = t6.table3_pin AND t1.status = t6.status 
LEFT JOIN table3__ AS t7 ON t6.pin = t7.pin AND t6.status = t7.status 
LEFT JOIN table3_ AS t8 ON t6.pin = t8.pin AND t6.status = t8.status 
LEFT JOIN table4 AS t9 ON t1.pin = t9.pin AND t1.status = t9.status 
LEFT JOIN table4_ AS t10 ON t1.pin = t10.pin AND t1.status = t10.status 
WHERE t1.pin LIKE '%1%' 
AND NOT EXISTS (
  SELECT * FROM tablep1 AS tp1 WHERE (
    tp1.pin = t1.pin AND tp1.year = t1.effectivity_year) 
  OR (tp1.pin = t4.pin AND tp1.year = t4.effectivity_year) 
  OR  (tp1.pin = t7.pin AND tp1.year = t7.effectivity_year) 
  OR (tp1.pin = t9.pin AND tp1.year = t9.effectivity_year))
AND t1.status = 'Active'

仅当 tablep1 table 中不存在 pin 号时，我才想从 table tables 中进行选择

感谢任何建议

Answer 1

请尝试在选择子查询中使用 tp1.pin 而不是 *。

SELECT `tp1.pin` FROM tablep1 AS tp1 WHERE (
    tp1.pin = t1.pin AND tp1.year = t1.effectivity_year) 
  OR (tp1.pin = t4.pin AND tp1.year = t4.effectivity_year) 
  OR  (tp1.pin = t7.pin AND tp1.year = t7.effectivity_year) 
  OR (tp1.pin = t9.pin AND tp1.year = t9.effectivity_year)