Single line subquery returns more than one row of Oracle Database
I have two tables, namely PESANMASUK and KONTAK. in PESANMASUK I have 5 applications: IDMASUK, TANGGALMASUK, JAM, NOMERHP, ISIPESAN . and KONTAK I have 3 files: IDKONTAK, NAMA, NOHP .
PESANMASUK table:
IDMASUK TANGGALMASUK JAM NOMERHP ISIPESAN
1 09/08/2015 09:00 +62847776 try sms
2 08/08/2015 10:00 +62856790 plase come in
3 08/08/2015 06:00 +6283444 you and me
KONTAK table data:
IDKONTAK NAMA NOHP
1 RIAN +62847776
2 ALFIAN +62856790
This is the result I want:
IDMASUK TANGGALMASUK JAM NOMERHP NAMA ISIPESAN
1 09/08/2015 09:00 +62847776 RIAN try sms
2 08/08/2015 10:00 +62856790 ALFIAN plase come in
3 08/08/2015 06:00 +6283444 you and me
this is my oracle request:
SELECT IDMASUK, TANGGALMASUK, JAM, NOMERHP, ISIPESAN,
(SELECT NAMA FROM KONTAK WHERE NOHP IN (SELECT NOMERHP FROM PESANMASUK)) AS NAMA
FROM PESANMASUK
I got the error:
ORA-01427: single-row subquery returns more than one row
I want to match between NOMERHP in PESANMASUK and NAMA in KONTAK and return all rows.
how can i solve this problem?
source to share
Try it. :)
The left join gets data or a row in the left table even if it doesn't have the corresponding data in the right table.
Please see the link below for more information on left join. http://www.w3schools.com/sql/sql_join_left.asp
select idmasuk, tanggalmasuk, jam, nomerhp, nama, isipesan
from
pesanmasuk
left join kontak
on pesanmasuk.nomerhp = kontak.nohp;
Hope this helps. Hooray!:)
source to share
As the error says, the subquery returns more than one row. Here are two ways to fix this:
SELECT IDMASUK, TANGGALMASUK, JAM, NOMERHP, ISIPESAN,
(SELECT MAX(NAMA) FROM KONTAK WHERE NOHP IN (SELECT NOMERHP FROM PESANMASUK)) AS NAMA
FROM PESANMASUK;
or
SELECT IDMASUK, TANGGALMASUK, JAM, NOMERHP, ISIPESAN,
(SELECT NAMA FROM KONTAK WHERE NOHP IN (SELECT NOMERHP FROM PESANMASUK WHERE ROWNUM = 1)) AS NAMA
FROM PESANMASUK;
My guess, however, is that you are not doing what you want and you really want a correlated subquery:
SELECT IDMASUK, TANGGALMASUK, JAM, NOMERHP, ISIPESAN,
(SELECT k.NAMA FROM KONTAK k WHERE k.NOHP = p.NOMERHP) AS NAMA
FROM PESANMASUK p
EDIT:
To get all names you can use listagg()
:
SELECT IDMASUK, TANGGALMASUK, JAM, NOMERHP, ISIPESAN,
(SELECT LISTAGG(k.NAMA, ',') WITHIN GROUP (ORDER BY k.NAMA)
FROM KONTAK k
WHERE k.NOHP = p.NOMERHP) AS NAMA
FROM PESANMASUK p
source to share