Reputation: 1178

Mule-Join two DB table results based on given condition

I need to join the output coming from two database tables based on given condition. The condition would be : If value for a column from first table is same as value for a column from second table, join the rows from both tables into one or else skip the row.

My code looks something like below :

    <?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:json="http://www.mulesoft.org/schema/mule/json" xmlns:dw="http://www.mulesoft.org/schema/mule/ee/dw" xmlns:db="http://www.mulesoft.org/schema/mule/db" xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
    xmlns:spring="http://www.springframework.org/schema/beans" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd
http://www.mulesoft.org/schema/mule/ee/dw http://www.mulesoft.org/schema/mule/ee/dw/current/dw.xsd
http://www.mulesoft.org/schema/mule/json http://www.mulesoft.org/schema/mule/json/current/mule-json.xsd">
    <db:mysql-config name="MySQL_Configuration" host="xyz.com" port="3306" user="userXXX" password="****" database="bd" doc:name="MySQL Configuration"/>
    <expression-filter expression="#[flowVars.payload1.seatsAvailable != flowVars.payload2.seatsAvailable]" name="Expression" doc:name="Expression"/>
    <flow name="studyFlow">
        <poll doc:name="Poll">
            <fixed-frequency-scheduler frequency="1" timeUnit="HOURS"/>
            <db:select config-ref="MySQL_Configuration" doc:name="Database">
                <db:parameterized-query><![CDATA[select * from table1]]></db:parameterized-query>
            </db:select>
        </poll>
        <set-variable variableName="payload1" value="#[payload]" doc:name="resultFromTable1"/>
        <db:select config-ref="MySQL_Configuration" doc:name="Database">
            <db:parameterized-query><![CDATA[select * from table2]]></db:parameterized-query>
        </db:select>
        <logger message="payload from american #[payload[0]]" level="INFO" doc:name="Logger"/>
        <set-variable variableName="payload2" value="#[payload]" doc:name="resultFromTable2"/>
        <dw:transform-message doc:name="Transform Message">
            <dw:set-payload><![CDATA[%dw 1.0
%output application/java
%var count = 0
---
flowVars.payload1 map using (id = $.code2) {

         (flowVars.payload2 filter ($.code2 contains id)  map {

            //col1:flowVars.payload2.code2,
            col2: flowVars.payload1.toAirport

     })
  }
  ]]></dw:set-payload>
        </dw:transform-message>

        <logger message="#[payload]" level="INFO" doc:name="Logger"/>
    </flow>
</mule>

But this doesn't give me appropriate output. Please help.

Upvotes: 0