Get data from 3 different tables

Question

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?

score 2 · Accepted Answer · edited May 23 '17 at 11:49

2

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

edited May 23 '17 at 11:49

Community

1
1

answered Aug 08 '14 at 14:21

Tomalak

332,285
67
532
628

Thanks. That works. Just one more thing, what if I want to get the results without the DG3\? – Rui Martins Aug 08 '14 at 14:34
1

I managed to get this, SELECT SUBSTR(USER_JN, INSTR(USER_JN, '\') + 1) from ami2_resource_item_jn UNION SELECT SUBSTR(USER_JN, INSTR(USER_JN, '\') + 1) from ami2_resource_item_dotnet_jn UNION SELECT SUBSTR(USER_JN, INSTR(USER_JN, '\') + 1) from AMI2_RESOURCE_ITEM_WAARDE_JN; – Rui Martins Aug 08 '14 at 14:38

Get data from 3 different tables

1 Answers1