Board logo

標題: [請教] mysql join search [打印本頁]
作者: carlkyo    時間: 2017-10-23 16:21     標題: [請教] mysql join search

  1. SELECT a.id,a.typeID,b.type,c.email_ok,d.esys_ok,e.reason FROM `cp_apply` a
  2. LEFT JOIN cp_type b ON(a.typeID = b.id)
  3. LEFT JOIN cp_email c ON(a.id = c.apply_id)
  4. LEFT JOIN cp_esys d ON(a.id = d.apply_id)
  5. LEFT JOIN cp_reject e ON(a.id = e.apply_id)
  6. WHERE a.typeID BETWEEN 3 AND 5
  7. AND a.centerID = 32
  8. AND e.reason IS NULL
複製代碼
請教下各位ching.
我以上的join出到以下既資料
[attach]2019273[/attach]
  1. SELECT a.id,a.typeID,b.type,c.email_ok,d.esys_ok,e.reason FROM `cp_apply` a
  2. LEFT JOIN cp_type b ON(a.typeID = b.id)
  3. LEFT JOIN cp_email c ON(a.id = c.apply_id)
  4. LEFT JOIN cp_esys d ON(a.id = d.apply_id)
  5. LEFT JOIN cp_reject e ON(a.id = e.apply_id)
  6. WHERE a.typeID BETWEEN 3 AND 5
  7. AND a.centerID = 32
  8. AND e.reason IS NULL
  9. AND c.email_ok != 2
  10. AND d.esys_ok != 2
複製代碼
但我唔想出email_ok & esys_ok 都係2既資料
改極都唔得
係唔係我本身既join錯左?
many thanks
作者: faiwaic    時間: 2017-10-23 16:49

請教下各位ching.
我以上的join出到以下既資料
但我唔想出email_ok & esys_ok 都係2既資料
改極都唔得
係唔 ...
carlkyo 發表於 2017-10-23 16:21


你唔係 join 錯, 係唔好 select 出黎就得....好 basic 野....(紅字 delete)
SELECT a.id,a.typeID,b.type,c.email_ok,d.esys_ok,e.reason FROM `cp_apply` a
LEFT JOIN cp_type b ON(a.typeID = b.id)
LEFT JOIN cp_email c ON(a.id = c.apply_id)
LEFT JOIN cp_esys d ON(a.id = d.apply_id)
LEFT JOIN cp_reject e ON(a.id = e.apply_id)
WHERE a.typeID BETWEEN 3 AND 5
AND a.centerID = 32
AND e.reason IS NULL
AND c.email_ok != 2
AND d.esys_ok != 2
作者: carlkyo    時間: 2017-10-23 19:42

回覆 2# faiwaic
  1. SELECT a.id,a.typeID,b.type,e.reason FROM `cp_apply` a
  2. LEFT JOIN cp_type b ON(a.typeID = b.id)
  3. LEFT JOIN cp_email c ON(a.id = c.apply_id)
  4. LEFT JOIN cp_esys d ON(a.id = d.apply_id)
  5. LEFT JOIN cp_reject e ON(a.id = e.apply_id)
  6. WHERE a.typeID BETWEEN 3 AND 5
  7. AND a.centerID = 32
  8. AND e.reason IS NULL
  9. AND c.email_ok != 2
  10. AND d.esys_ok != 2
複製代碼
  1. 沒有行。
複製代碼
作者: carlkyo    時間: 2017-10-23 20:46

本帖最後由 carlkyo 於 2017-10-23 20:47 編輯
  1. SELECT a.id,a.centerID,
  2. b.center_name,
  3. c.email_ok,
  4. d.esys_ok,
  5. e.reason
  6. FROM cp_apply a
  7. INNER JOIN cp_center b ON a.centerID = b.id
  8. LEFT JOIN cp_email c ON a.id = c.apply_id
  9. LEFT JOIN cp_esys d ON a.id = d.apply_id
  10. LEFT JOIN cp_reject e ON a.id = e.apply_id
  11. where c.email_ok != 2 and d.esys_ok != 2
複製代碼
出左
id        centerID        center_name        email_ok        esys_ok        reason
105               43                           其他                1                   1                   NULL
作者: carlkyo    時間: 2017-10-23 21:07

本帖最後由 carlkyo 於 2017-10-23 21:24 編輯
  1. SELECT t1.*,
  2. t2.center_name,
  3. t3.email_ok,
  4. t4.esys_ok,
  5. t5.reason
  6. FROM cp_apply t1
  7. JOIN cp_center t2 ON t1.centerID = t2.id
  8. left JOIN cp_email t3 ON t1.id = t3.apply_id
  9. left JOIN cp_esys t4 ON t1.id = t4.apply_id
  10. left JOIN cp_reject t5 ON t1.id = t5.apply_id
  11. where
  12. t3.email_ok is null and t4.esys_ok is null
  13. and
  14. t5.reason is NULL
  15. and
  16. t1.typeID BETWEEN 1 AND 2
  17. and
  18. t1.centerID = 32
  19. or
  20. t3.email_ok != 2 or t4.esys_ok != 2
  21. and
  22. t5.reason is NULL
  23. and
  24. t1.typeID BETWEEN 1 AND 2
  25. and
  26. t1.centerID = 32
複製代碼
唔知係唔係咁
many thanks
作者: faiwaic    時間: 2017-10-24 09:25

回覆 5# carlkyo

SELECT t1.*,
t2.center_name,
t3.email_ok,
t4.esys_ok,
t5.reason
FROM cp_apply t1
JOIN cp_center t2 ON t1.centerID = t2.id
left JOIN cp_email t3 ON t1.id = t3.apply_id
left JOIN cp_esys t4 ON t1.id = t4.apply_id
left JOIN cp_reject t5 ON t1.id = t5.apply_id
where
t3.email_ok is null and t4.esys_ok is null
and
t5.reason is NULL
and
t1.typeID BETWEEN 1 AND 2
and
t1.centerID = 32
or
t3.email_ok != 2 or t4.esys_ok != 2
and
t5.reason is NULL
and
t1.typeID BETWEEN 1 AND 2
and
t1.centerID = 32


你唔想 select (show) 出黎 同 個 value 本身係 吉 (null) 係兩回事喎  ching~
作者: carlkyo    時間: 2017-10-24 20:38

回覆 6# faiwaic


    我想搵t5.reason係null,t1.typeID BETWEEN 1 AND 2
t1.centerID = 32
t3.email_ok同t4.esys_ok都唔係2既資料
many thanks
作者: faiwaic    時間: 2017-10-25 10:30

回覆 7# carlkyo
  1. SELECT t1.*,t2.center_name,t3.email_ok,t4.esys_ok,t5.reason
  2. FROM cp_apply t1
  3. JOIN cp_center t2 ON t1.centerID = t2.id
  4. left JOIN cp_email t3 ON t1.id = t3.apply_id
  5. left JOIN cp_esys t4 ON t1.id = t4.apply_id
  6. left JOIN cp_reject t5 ON t1.id = t5.apply_id
  7. where
  8. t5.reason is NULL
  9. and t1.typeID BETWEEN 1 AND 2
  10. and t1.centerID = 32
  11. and t3.email_ok <> 2
  12. and t4.esys_ok <> 2
複製代碼
當D join 係對,
以你所求應該係咁咋喎~
作者: 紫河馬    時間: 2017-10-25 12:00

本帖最後由 紫河馬 於 2017-10-25 12:05 編輯

left join table (cp_email,cp_esys, cp_reject ) 既filter唔好放去where,
萬一cp_apply 有d record join唔到left table, 會因為where clause 而filter走(即係變左inner join...)
作者: carlkyo    時間: 2017-10-25 15:25

left join table (cp_email,cp_esys, cp_reject ) 既filter唔好放去where,
萬一cp_apply 有d record join ...
紫河馬 發表於 2017-10-25 12:00



cp_email,cp_esys, cp_reject 本身係冇record
我唔想顯示cp_apply join完cp_email,cp_esys 任何一個等於2既資料
many thanks




歡迎光臨 電腦領域 HKEPC Hardware (https://h2.hkepc.com/forum/) Powered by Discuz! 7.2