This question kind of links to my previous question.
I'm trying to break it down a little to just get the last two columns organized and without duplicates.
I'm attempting to do something similar to the top rated solution on this site.
I'm using excels query builder to try and do this as I am not that familiar beyond simple basics of sql and databases.
The statement is currently:
SELECT WORK_CENTER.EQNO,
(
SELECT
UD_DATA.CUSER AS U1
FROM UD_DATA
FULL OUTER JOIN
WORK_CENTER
ON UD_DATA.PARENT_ID = WORK_CENTER.ID
LEFT JOIN
UD_COLS
ON UD_DATA.UD_COLS_ID = UD_COLS.ID
WHERE UD_COLS.ID='16'
),
(
SELECT
UD_DATA.CUSER AS U2
FROM UD_DATA
FULL OUTER JOIN WORK_CENTER ON UD_DATA.PARENT_ID = WORK_CENTER.ID
LEFT JOIN UD_COLS ON UD_DATA.UD_COLS_ID = UD_COLS.ID
WHERE UD_COLS.ID='17'
)
FROM
IQMS.UD_DATA UD_DATA
FULL OUTER JOIN
IQMS.WORK_CENTER WORK_CENTER
ON WORK_CENTER.ID=UD_DATA.PARENT_ID
LEFT OUTER JOIN
IQMS.UD_COLS UD_COLS
ON
UD_DATA.UD_COLS_ID = UD_COLS.ID
ORDER BY WORK_CENTER.EQNO
Desired Result:
EQNO | U1 | U2
-----+------+------
001 | 1000 | test1
002 | 2000 | test2
003 | 3000 | test3
004 | 4000 | test4
But I'm currently getting
ORA-01427: single-row subquery returns more than one row Thanks to Pattrick Bacon. Pointed out my overlooking having LEFT JOIN JOIN
In the linked question it was suggested that the cause of the wrong output was due to the two tables being cross joined, I tried to get an understanding of it online and I was unable to really see a solution around it except for trying this.
I haven't changed too much of the subqueries themselves but I'm obviously doing something wrong. would anyone be able to help?
Someone asked for more examples
From my linked question:
This is the desired result(not showing blank columns and apologies for badly formatted table):
EQNO | CNTR_TYPE | CNTR_DESC | MFGCELL | MFG_TYPE | CUSER | U2
001 | 110T-40MM | DEMAG SYSTEM 110-430 | MOLDING | INJECTION | 1000 | test1
002 | 150T-25MM | DEMAG SYSTEM 150-320 | MOLDING | INJECTION | 2000 | test2
003 | 150T-45MM | DEMAG SYSTEM 150-610 | MOLDING | INJECTION | 3000 | test3
005 | 150T-45MM | DEMAG EXTRA 500-610 | MOLDING | INJECTION | 4000 | test4
But what I'm getting is:
EQNO | CNTR_TYPE | CNTR_DESC | MFGCELL | MFG_TYPE | CUSER | U2 | E2
001 | 110T-40MM | DEMAG SYSTEM 110-430 | MOLDING | INJECTION | 1000 | test1 | 001
001 | 110T-40MM | DEMAG SYSTEM 110-430 | MOLDING | INJECTION | 1000 | test2 | 002
001 | 110T-40MM | DEMAG SYSTEM 110-430 | MOLDING | INJECTION | 1000 | test3 | 003
001 | 110T-40MM | DEMAG SYSTEM 110-430 | MOLDING | INJECTION | 1000 | test4 | 005
002 | 150T-25MM | DEMAG SYSTEM 150-320 | MOLDING | INJECTION | 2000 | test1 | 001
002 | 150T-25MM | DEMAG SYSTEM 150-320 | MOLDING | INJECTION | 2000 | test2 | 002
002 | 150T-25MM | DEMAG SYSTEM 150-320 | MOLDING | INJECTION | 2000 | test3 | 003
002 | 150T-25MM | DEMAG SYSTEM 150-320 | MOLDING | INJECTION | 2000 | test4 | 005
003 | 150T-45MM | DEMAG SYSTEM 150-610 | MOLDING | INJECTION | 3000 | test1 | 001
003 | 150T-45MM | DEMAG SYSTEM 150-610 | MOLDING | INJECTION | 3000 | test2 | 002
003 | 150T-45MM | DEMAG SYSTEM 150-610 | MOLDING | INJECTION | 3000 | test3 | 003
003 | 150T-45MM | DEMAG SYSTEM 150-610 | MOLDING | INJECTION | 3000 | test4 | 005
005 | 150T-45MM | DEMAG EXTRA 500-610 | MOLDING | INJECTION | 4000 | test1 | 001
005 | 150T-45MM | DEMAG EXTRA 500-610 | MOLDING | INJECTION | 4000 | test2 | 002
005 | 150T-45MM | DEMAG EXTRA 500-610 | MOLDING | INJECTION | 4000 | test3 | 003
005 | 150T-45MM | DEMAG EXTRA 500-610 | MOLDING | INJECTION | 4000 | test4 | 005
Union doesn't really work as it just puts the two columns "CUSER" and "CUSER AS U2" together since they are the same column really.
Adding more sample data
Here's a bit from the UD_DATA table
ID | UD_COLS_ID|PARENT_ID|CUSER
-------+-----------+---------+------
12312 | 16 | 5210 | 1000
12313 | 17 | 5210 | test1
12314 | 16 | 5212 | 2000
12315 | 17 | 5212 | test2
12316 | 16 | 5213 | 3000
12317 | 17 | 5213 | test4
12318 | 16 | 5214 | 4000
12319 | 17 | 5214 | test4
My main issue is just displaying the date from the CUSER column as
|COL1 | COL2|
+-----+-----+
| 1000|test1|
| 2000|test2|
| 3000|test3|
| 4000|test4|
But from the linked question I got something like:
|COL1 | COL2|
+-----+-----+
| 1000|test1|
| 1000|test2|
| 1000|test3|
| 1000|test4|
| 2000|test1|
| 2000|test2|
| 2000|test3|
| 2000|test4|
| 3000|test1|
| 3000|test2|
| 3000|test3|
| 3000|test4|
This is what I've been struggling with trying to fix
You're actually after a PIVOT; no need for the joins, etc.
E.g.: