Clever Geek Handbook

Apply if row has function to the left

I am joining 3-6 tables using a left join. Here is a sample code from the section

     FROM ip_ucp_01
LEFT JOIN ip_lt_01 
       ON ip_ucp_01.time = ip_lt_01.time 
      AND ip_ucp_01.date = ip_lt_01.date
LEFT JOIN ip_le 
       ON ip_lt_01.time = ip_le.time 
      AND ip_lt_01.date = ip_le.date
LEFT JOIN ip_lmiv_01 
       ON ip_le.time = ip_lmiv_01.time 
      AND ip_le.date = ip_lmiv_01.date
LEFT JOIN ip_cwg 
       ON ip_lmiv_01.time = ip_cwg.time 
      AND ip_lmiv_01.date = ip_cwg.date
LEFT JOIN ip_mtu_01 
       ON ip_cwg.time = ip_mtu_01.time 
      AND ip_cwg.date = ip_mtu_01.date

if there is no row in second table and third table, data table, etc. will not be displayed. I need to check if a row exists in the second table or not. If so, it will use the second table of the second join, unless it uses the left join on the third table, and so on.

+3


source to share


3 answers


How about if you just join the first table? Like this:

     FROM ip_ucp_01
LEFT JOIN ip_lt_01 
       ON ip_ucp_01.time = ip_lt_01.time 
      AND ip_ucp_01.date = ip_lt_01.date
LEFT JOIN ip_le 
       ON ip_ucp_01.time = ip_le.time 
      AND ip_ucp_01.date = ip_le.date
LEFT JOIN ip_lmiv_01 
       ON ip_ucp_01.time = ip_lmiv_01.time 
      AND ip_ucp_01.date = ip_lmiv_01.date
LEFT JOIN ip_cwg 
       ON ip_ucp_01.time = ip_cwg.time 
      AND ip_ucp_01.date = ip_cwg.date
LEFT JOIN ip_mtu_01 
       ON ip_ucp_01.time = ip_mtu_01.time 
      AND ip_ucp_01.date = ip_mtu_01.date


since you are only joining the date and time, wouldn't that do what you want?

0


source


I don't think the LEFT JOIN will work the way you ask, because if there is no record in the parent table, you won't be able to get the data from the grandchild.



In my opinion, you can just use CASE with Count (not the best way), but that will solve your problem.

0


source


You can use separate queries SELECT

for these JOIN

and UNION

all of them (with DISTINCT

) in one query, for example:

SELECT DISTINCT column_name
FROM (
 SELECT column_name
 FROM ip_ucp_01
  LEFT JOIN ip_lt_01 ON ip_ucp_01.time = ip_lt_01.time AND ip_ucp_01.date =   ip_lt_01.date
  LEFT JOIN ip_le ON ip_lt_01.time = ip_le.time AND ip_lt_01.date = ip_le.date;

  UNION

  SELECT column_name
  FROM ip_ucp_01
  LEFT JOIN ip_lmiv_01 ON ip_ucp_01.time = ip_lmiv_01.time AND ip_ucp_01.date =   ip_lmiv_01.date

) a;
0


source






More articles:


All Articles