title: concat columns by joining multiple DataFrames
body: <p>I have multiple dataframes I need to concat the addresses and zip based condition.Actually I had sql query which i need to convert to dataframe join
  I had written UDF which is working fine for concating multiple columns to obtain a single column,</p>
  
  <pre><code>val getConcatenated = udf( (first: String, second: String,third: String,fourth: String,five: String,six: String) =&gt; { first + "," + second + "," +third + "," +fourth + "," +five + "," +six } )
  </code></pre>
  
  <p>MySQl Query</p>
  
  <pre><code>  select 
  CONCAT(al.Address1,',',al.Address2,',',al.Zip) AS AtAddress,
  CONCAT(rl.Address1,',',rl.Address2,',',rl.Zip) AS RtAddress,
  CONCAT(d.Address1,',',d.Address2,','d.Zip) AS DAddress,
  CONCAT(s.Address1,',',s.Address2,',',s.Zip) AS SAGddress,
  CONCAT(vl.Address1,',',vl.Address2,',vl.Zip) AS VAddress,
  CONCAT(sg.Address1,',',sg.Address2,',sg.Zip) AS SAGGddress
  FROM
  si s inner join 
  at a on s.cid = a.cid and s.cid =a.cid
  inner join De d on s.cid = d.cid AND d.aid = a.aid 
  inner join SGrpM sgm on s.cid = sgm.cid and s.sid =sgm.sid and sgm.status=1
  inner join SeG sg on sgm.cid =sg.cid and sgm.gid =sg.gid 
  inner join bd bu on s.cid = bu.cid and s.sid =bu.sid
  inner join locas al on a.ALId = al.lid
  inner join locas rl on a.RLId = rl.lid
  inner join locas vl on a.VLId = vl.lid
  </code></pre>
  
  <p>I am facing issue when joining the dataframes which gives me null value. </p>
  
  <pre><code>val DS = DS_SI.join(at,Seq("cid","sid"),"inner").join(DS_DE,Seq("cid","aid"),"inner") .join(DS_SGrpM,Seq("cid","sid"),"inner").join(DS_SG,Seq("cid","gid"),"inner") .join(at,Seq("cid","sid"),"inner")
  .join(DS_BD,Seq("cid","sid"),"inner").join(DS_LOCAS("ALId") &lt;=&gt; DS_LOCATION("lid") &amp;&amp; at("RLId") &lt;=&gt; DS_LOCAS("lid")&amp;&amp; at("VLId") &lt;=&gt; DS_LOCAS("lid"),"inner")
  </code></pre>
  
  <p>Iam trying to join my dataFrames like above which is not giving be proper results and then I want to concat by adding the column
      .withColumn("AtAddress",getConcatenated())
      .withColumn("RtAddress",getConcatenated())....</p>
  
  <p>Any one tell me how effectively we can achieve this and am I joining the dataframes correctly or any better approach for this .....</p>