CODEWITHSUNDEEP
sqloracle-databasegroup-by
Majestic
Majestic

Reputation: 938

Group by several results SQL Oracle

I'd like to have a result grouped by a propertie.

Here's an example about what I would like to retrieve: enter image description here

And here's come the table definition : enter image description here

I tried this but it does not work :

SELECT      OWNER.NAME, DOG.DOGNAME
WHERE       OWNER.ID = DOG.OWNER_ID
AND         OWNER.NAME = (SELECT OWNER.NAME FROM OWNER);

But it returns me an error:

  1. 00000 - "single-row subquery returns more than one row"

Thanks a lot !

Upvotes: 0

Views: 79

Answers (3)

Alex Mantaut
Alex Mantaut

Reputation: 3875

Edit Based on Alex's response, a modified version of the query would be:

SELECT OWNER.NAME, DOG.DOGNAME FROM OWNER LEFT JOIN DOG ON OWNER.ID = DOG.OWNER_ID ORDER BY OWNER.NAME

Upvotes: 1

Alex
Alex

Reputation: 17289

I am not Oracle expert, but I believe you need FROM and JOIN part :-) :

http://sqlfiddle.com/#!4/f8630/1

SELECT      OWNER.ID, OWNER.NAME,
            DOG.ID, DOG.DOGNAME
FROM        OWNER
LEFT JOIN   DOG
ON          OWNER.ID = DOG.OWNER_ID;

Upvotes: 1

Michael Broughton
Michael Broughton

Reputation: 4055

First up, you are missing the table clauses:

And if you put those in, you still have a problem if you have multiple owners with the same name, where the sub-select in the where clause is returning multiple rows:

AND OWNER.NAME = (SELECT OWNER.NAME FROM OWNER);

If NAME is not distinct, this raises the error.

You could change it to:

AND OWNER.NAME IN (SELECT OWNER.NAME FROM OWNER);

Although in this case the entire clause is meaningless as the whole table will always be returned

Final answer:

SELECT      OWNER.NAME, DOG.DOGNAME
FROM        OWNER, DOG
WHERE       OWNER.ID = DOG.OWNER_ID;

Upvotes: 0

Related Questions