SQL Query Join Tables

Question

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

Without seeing the data, it is hard to tell you why exactly, but, start by looking in the tables to see where the duplicate data would come from. Alternatively, you could try using `DISTINCT`, but that is masking the issue. — Keith, Dec 04 '19 at 08:05
The data is the returned results, I can't use distinct because the address no is 1. It will return only one address no, 1 and email: kinki@hostpro2u.com — Synetrix, Dec 04 '19 at 08:10
I think as per your expected result from result you need to use "pivot" in query for convert row's to column for last column. you can use reference: [link](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server/15931734) — Shusang, Dec 04 '19 at 08:15
@Synetrix sorry, I mean, without seeing the data in all the tables, and `DISTINCT` should look at the whole row, not only certain criteria `The query uses the combination of values in all specified columns in the SELECT list to evaluate the uniqueness.` — Keith, Dec 04 '19 at 08:22

score 1 · Accepted Answer · answered Dec 04 '19 at 08:19

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).

This seems to be what I need, may I know what is the purpose of MAX and END in the case statement — Synetrix, Dec 04 '19 at 08:30
@Synetrix all `CASE` expressions need an `END`; if you don't have an `ELSE` then when the condition is not true the result of the `CASE` expression is `NULL`, which is ignored by `MAX`. So basically the combination of the `MAX` and the `CASE` with no `ELSE` clause means that for each expression, we **only** get the value which matches the condition. Note that since we are only getting one value, we could just as easily use `MIN` as `MAX`. I hope this helps... — Nick, Dec 04 '19 at 09:15

SQL Query Join Tables

1 Answers1