0

I am trying to write a query that involves 3 joins, and one of the joins is on a table that has multiple records on the same column, I am trying to return all results in a single row. So if I have the 3 tables:

Table 1 
| order_ID | order_number
+----------| ---------
|  1       | 4384368434
|  2       |5343443584
|  3       |584364684
+----------+-----------

Table 2
+--------------+--------------------+-----------------
| order_seq_id | data_dictionary_id | node_value_text |
+--------------+--------------------+-----------------
|            1 |                101 |Value
|            2 |                105 |Value
|            3 |                110 |Value
+--------------+--------------------+----------------
table 3

+--------------------+--------------------------+
| data_dictionary_id | DATA_DICTIONARY_MNEMONIC |
+--------------------+--------------------------+
|                101 | 'InitialOrderNumber'     |
|                105 | 'InitialOrderDueDate'    |
|                110 | 'OrderDetails'           |
+--------------------+--------------------------+

The returned line I want is:

Order_ID | DATA_DICTIONARY_MNEMONIC 1| node_value_text 1 | DATA_DICTIONARY_MNEMONIC 2 | node_value_text 2 | 
-------------------------------------------------------------------------------------- | ------------------------
 1       | InitialOrderNumber        |  '1256214214'    |  InitialOrderDueDate         | 'Jan 1st'

So I want to return a single row if possible that has all the rows from table 1 (there is more than what I have above) plus 2 more columns that contain the value of node_value_text in table 2 of InitialOrderNumber and InitialOrderDueDate. Order_ID is joined on order_seq_id, which is joined to table 3 using data_dictionary_id in order to look up which data_dictionary_id is used for InitialOrderNumbers and duedates.

I have tried joining the same table like so:

 SELECT a.order_number, b.order_seq_id, b.node_value_text, 
c.DATA_DICTIONARY_MNEMONIC, d.DATA_DICTIONARY_MNEMONIC
FROM table1 a
INNER JOIN table2 b
ON a.order_id = b.order_seq_id
INNER JOIN table3 c
ON B.DATA_DICTIONARY_ID = c.data_dictionary_id
INNER JOIN om_prod.om_order_data_dictionary d
ON B.DATA_DICTIONARY_ID = d.data_dictionary_id
WHERE a.order_id like '3035880'
AND c.DATA_DICTIONARY_MNEMONIC ='InitialOrderNumber'
AND d.DATA_DICTIONARY_MNEMONIC ='InitialOrderDueDate';

but it is unsuccessful. Am I on the right track with a double join? Or is there a better way to do this?

WHERE a.order_id like '3035880'is used for testing.

Ranch Dubois
  • 55
  • 7
  • It's not easy to understand your question when there are columns in your query that are not in the table definitions and the sample data doesn't suggest how you are supposed to get 2 rows from the last table for each row in the first and second table. Please remove irrelevant columns from the query and make sure your sample data matches the expected result – Joakim Danielson Aug 27 '19 at 20:13
  • Just curious: _why_ would you want to do this? It seems like you're trying to solve a _presentation layer_ problem on the data-access layer. See also [this thread](https://stackoverflow.com/questions/47993215/convert-multiple-rows-with-multiple-column-into-one-record-in-oracle); Oracle has a fixed limit on row lengths. – Mick Mnemonic Aug 27 '19 at 20:14
  • Sorry, fixed columns – Ranch Dubois Aug 27 '19 at 20:17

0 Answers0