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

Question

SQL FIDDLE

I want to select from a few table only if they dont exist in another table my select query is like this

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'

I just want to select from table tables only if the pin number is not present in tablep1 table

Any suggestion is appreciated

Answer 1

Please try with tp1.pin instead of * in select sub query .

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)