single-row subquery returns more than one row Oracle Database [solved]

Question

0.00/5 (No votes)

See more:

i have two tables namely PESANMASUK anda KONTAK. in PESANMASUK i have 5 fileds : IDMASUK, TANGGALMASUK, JAM, NOMERHP, ISIPESAN . and KONTAK i have 3 fileds : IDKONTAK, NAMA, NOHP.

PESANMASUK table :

CSS

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 :

CSS

IDKONTAK	NAMA		NOHP
1		RIAN		+62847776
2		ALFIAN		+62856790

and this result is i want :

CSS

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 query :

SQL

SELECT IDMASUK, TANGGALMASUK, JAM, NOMERHP, ISIPESAN, 
(SELECT NAMA FROM KONTAK WHERE NOHP IN (SELECT NOMERHP FROM PESANMASUK)) AS NAMA 
FROM PESANMASUK

I got error like this :

SQL

ORA-01427: single-row subquery returns more than one row

I wanna matching between NOMERHP in PESANMASUK and NAMA in KONTAK and return all rows.
how can I solve this??

Posted 9-Aug-15 20:09pm

aminvincent

Updated 1-Mar-21 9:52am

v2

Add a Solution

2 solutions

Solution 1

SQL

SELECT IDMASUK, TANGGALMASUK, JAM, NOMERHP, ISIPESAN,
(SELECT NAMA FROM KONTAK WHERE NOHP IN (SELECT NOMERHP FROM PESANMASUK) and ROWNUM <= 1) AS NAMA
FROM PESANMASUK

please try this

Posted 9-Aug-15 20:19pm

Praveen_Kumar Gupta

Comments

aminvincent 10-Aug-15 2:25am

Praveen : i have tried your query above but result NAMA replace just one name,...any solution else?

[no name] 10-Aug-15 4:23am

SELECT IDMASUK, TANGGALMASUK, JAM, NOMERHP, ISIPESAN,
(SELECT listagg(NAMA,',') within group (order by null) FROM KONTAK WHERE NOHP IN (SELECT NOMERHP FROM PESANMASUK)) AS NAMA
FROM PESANMASUK

This will return you more records in comma seperated

aminvincent 10-Aug-15 20:17pm

praveen : thanks for your help,.but now i have fixed it and my query work...
this my query : SELECT IDMASUK, TANGGALMASUK, JAM, NOMERHP, ISIPESAN, NAMA
FROM PESANMASUK
LEFT JOIN KONTAK
ON KONTAK.NOHP = PESANMASUK.NOMERHP
ORDER BY TANGGALMASUK DESC, JAM DESC

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2015-08-09T22:05:00

Solution 2

Basically, you need to join data[^]:

SQL

SELECT Table1.Field1, Table2.Field1
FROM Table1 INNER JOIN Table2 ON Table1.PK = Table2.FK

Where
PK = Primary Key[^],
FK = Foreign Key[^].

For further information, please see: Visual Representation of SQL Joins[^]

Posted 9-Aug-15 22:05pm

Maciej Los

Comments

aminvincent 10-Aug-15 20:18pm

in those table no relation,...finally i used this query and it worked :
SELECT IDMASUK, TANGGALMASUK, JAM, NOMERHP, ISIPESAN, NAMA
FROM PESANMASUK
LEFT JOIN KONTAK
ON KONTAK.NOHP = PESANMASUK.NOMERHP
ORDER BY TANGGALMASUK DESC, JAM DESC

Maciej Los 11-Aug-15 2:15am

So, you did what i suggested. Please, accept my answer as a solution (green button) - formally to remove question drom unanswered list.