CODEWITHSUNDEEP
sqloracle-database
Rui Martins
Rui Martins

Reputation: 2184

Get data from 3 different tables

I have 3 tables in my Oracle DB, namely

AMI2.AMI2_RESOURCE_ITEM_JN

  ID                               NUMBER(18,0)          NULL,
  TYPE_CODE                        VARCHAR2(20 BYTE)     NULL,
  USER_JN                          VARCHAR2(32 BYTE)     NOT NULL,

AMI2_RESOURCE_ITEM_DOTNET_JN

  ID                               NUMBER(18,0)          NULL,
  NAAM                             VARCHAR2(500 BYTE)    NULL,
  USER_JN                          VARCHAR2(32 BYTE)     NOT NULL,

AMI2.AMI2_RESOURCE_ITEM_WAARDE_JN

  ID                               NUMBER(18,0)          NULL,
  WAARDE                           CLOB                  NULL,
  USER_JN                          VARCHAR2(32 BYTE)     NOT NULL,

What I need is to join this tables and get the distinct user_jn of all tables.

AMI2.AMI2_RESOURCE_ITEM_JN

ID   TYPE_CODE      USER_JN
167  DOTNET_GLOBAL  DG3\PAULB
168  DOTNET_GLOBAL  DG3\JOSBRA
169  DOTNET_GLOBAL  DG3\JOSBRA
170  DOTNET_GLOBAL  DG3\RONASS
171  DOTNET_GLOBAL  DG3\JOSBRA
199  DOTNET_GLOBAL  DG3\RUIMAR
162  DOTNET_GLOBAL  DG3\DIMITRI
201  DOTNET_GLOBAL  DG3\RUIMAR
204  DOTNET_GLOBAL  DG3\RUIMAR
207  DOTNET_GLOBAL  DG3\DIMITRI

AMI2_RESOURCE_ITEM_DOTNET_JN

ID   NAAM             USER_J
167  MaatregelWaarde  DG3\JOSBRA
168  MaatregelWaarde  DG3\JOSBRA 
169  MaatregelWaarde  DG3\JOSBRA 
170  MaatregelWaarde  DG3\JOSBRA 
171  MaatregelWaarde  DG3\JOSBRA 
199  MaatregelWaarde  DG3\RUIMAR 
162  MaatregelWaarde  DG3\RUIMAR

AMI2.AMI2_RESOURCE_ITEM_WAARDE_JN

ID   Waarde     USER_J
149 (HUGECLOB)  DG3\JOSBRA
150 (HUGECLOB)  DG3\JOSBRA
151 (HUGECLOB)  DG3\JOSBRA
152 (HUGECLOB)  DG3\JOSBRA
153 (HUGECLOB)  DG3\JOSBRA
158 (HUGECLOB)  DG3\RUIMAR

So, with this 3 tables, I need to get 

USER_J
DG3\DIMITRI
DG3\JOSBRA
DG3\PAULB
DG3\RONASS
DG3\RUIMAR

I'm not very good at this, so I don't even know where to start. Any ideas?

Upvotes: 0

Views: 68

Answers (1)

Tomalak
Tomalak

Reputation: 338148

SELECT USER_JN FROM AMI2.AMI2_RESOURCE_ITEM_JN
UNION
SELECT USER_JN FROM AMI2_RESOURCE_ITEM_DOTNET_JN
UNION
SELECT USER_JN FROM AMI2.AMI2_RESOURCE_ITEM_WAARDE_JN

Hint: The result of a UNION is distinct by default. (Use UNION ALL if you want duplicates at some point.)

When it comes to sorting the result, refer to SQL Query - Using Order By in UNION

Upvotes: 2

Related Questions