Search code examples
sqlsql-serverjdedwards

SQL Query Join Tables

I am trying to join tables based on the primary key of address no, 1. However, the issue below creates duplicate record for the same address no. 003 78057911 (wpphtp !=F) and 003 78057922 (wpphtp = F) are the same fields in the database, however I want to extract it on a different column. How can I do so?

 select ABAN8,'CREATE',ABALPH, '','', '','','','','','', EAEMAL,'',
                 CASE
                      WHEN wpphtp != 'F'
                         THEN WPAR1
          else ' '
                 END AS prefix ,'', CASE
                      WHEN wpphtp != 'F'
                         THEN WPph1
          else ' '
                 END AS phone, '',CASE
                      WHEN wpphtp = 'F'
                         THEN wpar1
          else ' '
                 END AS prefixfax,' ',CASE
                      WHEN wpphtp = 'F'
                         THEN wpph1
          else ' '
                 END AS fax  from PRODDTA.F0111 
    join PRODDTA.F01151 on proddta.f0111.wwan8 = PRODDTA.F01151.EAAN8   
    join PRODDTA.F0115 JOIN PRODDTA.F0101 ON PRODDTA.F0115.wpAN8 = PRODDTA.F0101.ABAN8  
    ON PRODDTA.F0115.wpAN8 = PRODDTA.F0111.wwAN8 where wwidln < 1
   order by wwan8

Result:

1 | CREATE | E-GLOBAL INNOVATIVE SDN BHD | kinki@hostpro2u.com   | 003 | 78057911
1 | CREATE | E-GLOBAL INNOVATIVE SDN BHD | billing@hostpro2u.com | 003 | 78057911
1 | CREATE | E-GLOBAL INNOVATIVE SDN BHD | kinki@hostpro2u.com   | 003 | 78057922
1 | CREATE | E-GLOBAL INNOVATIVE SDN BHD | billing@hostpro2u.com | 003 | 78057922

Expected Result:

1   CREATE | E-GLOBAL INNOVATIVE SDN BHD | kinki@hostpro2u.com   | 003 | 78057911 | 003 | 78057922
1   CREATE | E-GLOBAL INNOVATIVE SDN BHD | billing@hostpro2u.com | 003 | 78057911 | 003 | 78057922
Solution

  • You can use conditional aggregation to get the values for different wpphtp in different columns. Without seeing table structures and sample data it's hard to be 100% certain but this should work:

    select ABAN8,'CREATE',ABALPH, '','', '','','','','','', EAEMAL,'',
       MAX(CASE WHEN wpphtp != 'F' THEN WPAR1 END) AS prefix,'',
       MAX(CASE WHEN wpphtp != 'F' THEN WPph1 END) AS phone, '',
       MAX(CASE WHEN wpphtp = 'F'  THEN wpar1 END) AS prefixfax,' ',
       MAX(CASE WHEN wpphtp = 'F'  THEN wpph1 END) AS fax
from PRODDTA.F0111 
join PRODDTA.F01151 on proddta.f0111.wwan8 = PRODDTA.F01151.EAAN8   
join PRODDTA.F0115 JOIN PRODDTA.F0101 ON PRODDTA.F0115.wpAN8 = PRODDTA.F0101.ABAN8  
  ON PRODDTA.F0115.wpAN8 = PRODDTA.F0111.wwAN8 where wwidln < 1
GROUP BY -- add all the other column names here
order by wwan8

    In the GROUP BY clause, you need to add all the other column names (i.e. everything except prefix, phone, prefixfax and fax).