# CloverETL - MSSQL 2012 Select statements and JOINs

**URL:** <https://forum.cloverdx.com/t/cloveretl-mssql-2012-select-statements-and-joins/1811>\
**Category:** CloverDX Platform\
**Created:** [November 18, 2016, 12:00am UTC](https://forum.cloverdx.com/t/cloveretl-mssql-2012-select-statements-and-joins/1811 "2016-11-18T00:00:00Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![mrhong13371](https://avatars.discourse-cdn.com/v4/letter/m/a5b964/32.png) [@mrhong13371](https://forum.cloverdx.com/u/mrhong13371)\
**Post date:** [November 18, 2016, 12:00am UTC](https://forum.cloverdx.com/t/cloveretl-mssql-2012-select-statements-and-joins/1811/1 "2016-11-18T00:00:00Z")

</div>

Hey there,  
I have an SQL statement which I want to “translate” into CloverETL. So basically I have an SQL statement similar to:

```auto

Select 
	Professoren.persnr AS [Prof PersNr]
INTO
	[Prof - Assistent]
FROM
	Professoren

LEFT JOIN Assistenten
ON Boss

```

So I am a little bit stuck here. I tried to use official user guide and followed following approach:  
1. Created DBInputTable containing SQL query:

```auto
SELECT * from professoren

```

2. Creating DBJoin with Join key _persnr_ and SQL query

```auto
SELECT * from assistenten WHERE boss = ?

```

(Assistenten.boss is foreign key from Professoren.persnr)

3. Creating incoming edge into DBJoin w/ metadata from professoren containing only _persnr_  
Creating outgoing edge from DBJoin w/ metadata from professoring containing only _persnr_

Note: I used same DB connection for DBInputTable and DBJoin. also have a look at database schema.

Thank in advance for any hint! I am really stuck on this one!

- [uni schema.PNG](https://cdn.cloverdx.com/forum/18308%5F41ae5038d39cd2230512e5353497aba4/uni%5Fschema.PNG)

---

<div class="post-metadata">

**Author:** ![PedroVazquezRosario](https://avatars.discourse-cdn.com/v4/letter/p/ed8c4c/32.png) [@PedroVazquezRosario](https://forum.cloverdx.com/u/PedroVazquezRosario)\
**Post date:** [November 21, 2016, 4:10pm UTC](https://forum.cloverdx.com/t/cloveretl-mssql-2012-select-statements-and-joins/1811/2 "2016-11-21T16:10:22Z")

</div>

Hi mrhong13371,

Could you please tell me what the expected output of this use case will be?

---

<div class="post-metadata">

**Author:** ![mrhong13371](https://avatars.discourse-cdn.com/v4/letter/m/a5b964/32.png) [@mrhong13371](https://forum.cloverdx.com/u/mrhong13371)\
**Post date:** [November 22, 2016, 8:31am UTC](https://forum.cloverdx.com/t/cloveretl-mssql-2012-select-statements-and-joins/1811/3 "2016-11-22T08:31:18Z")

</div>

HI vazquezrosariop,

thanks for your reply! This is just an generic example. At the end of the day, I want to join two different tables on one key and I do not understand how to realize this example w/ CloverETL.

Thank you for your time!

Best regards,  
Shun Long

---

<div class="post-metadata">

**Author:** ![PedroVazquezRosario](https://avatars.discourse-cdn.com/v4/letter/p/ed8c4c/32.png) [@PedroVazquezRosario](https://forum.cloverdx.com/u/PedroVazquezRosario)\
**Post date:** [November 22, 2016, 3:37pm UTC](https://forum.cloverdx.com/t/cloveretl-mssql-2012-select-statements-and-joins/1811/4 "2016-11-22T15:37:38Z")

</div>

Hi,

You seem to be on the correct track, you need to access the Transformation editor in the DBJoin to map the input values to the output values from both databases.

Another option to your use case would be to add a second DBInputTable containing SQL query:

```auto
Select * from assistenten

```

and use a ExtMergeJoin to join the two input streams by using the appropriate Join Key. Finally, use the Transformation editor to map the input metadata to the correct output metadata.

I have taken the liberty of attaching a project with both use case implemented.

[DatabaseExample.zip](https://cdn.cloverdx.com/forum/18200%5F82620aaba9c678ce7b0c9c21dc806178/DatabaseExample.zip)

- [DatabaseExample.zip](https://cdn.cloverdx.com/forum/18200%5F82620aaba9c678ce7b0c9c21dc806178/DatabaseExample.zip)