Bring only one line in subquery

Asked Oct 23 '13 at 13:45

Active Oct 24 '13 at 07:40

Viewed 93 times

I'm am doing a SELECT query, that bring back a car, and some of its pieces it need to repair.

So I'm doing linke this :

SELECT id_car, concat((select pi.name_piece from t_piece pi LEFT JOIN t_piece_car pc on pc.id_piece = pi.id_piece where pc.id_car = 222), ',') as listPiece
from car 
where id_car = 222

piece contains all possible pieces. piece_car is a link between my car and piece. So it contains all pieces my car needs.

The problem here is, the subquery bring more than one line. I tried to concat inside the subquery, but nothing better. My car need 3 pieces, so it bring back 3 lines. I have to bring back one line, like this :

'piece1, piece2, piece3' as listPiece

But I always got 3 lines.

This is the subQuery that need to bring back only one line, with a concat of all the pieces names of my car, not the principal query.

UPDATE : Finally found another question here :

https://stackoverflow.com/a/1076237/1439453

Considering, that my answer is not the same that in this question, I don't really know if it's needed to let this question open, but necessary to let exists.

How can I do this.

Thank you.

edited May 23 '17 at 12:05

Community

asked Oct 23 '13 at 13:45

provençal le breton

1,428
4
26
43

2

If you're using 11g use `LISTAGG()` and `GROUP BY` – peterm Oct 23 '13 at 13:49
Not in Oracle @huMptyduMpty. – Ben Oct 23 '13 at 13:50
http://explainextended.com/2009/04/05/group_concat-in-oracle-10g/ – Raphaël Althaus Oct 23 '13 at 13:51
@peterm I'm using 10g, but this is a start for me. – provençal le breton Oct 23 '13 at 13:58
@Zaphod Try `WM_CONCAT()` then – peterm Oct 23 '13 at 14:07
@peterm Yes, just found out `WM_CONCAT()` , in another question. Add a link in my update. Guess I will add a space between two elements later in t he code. – provençal le breton Oct 23 '13 at 14:08

Bring only one line in subquery

0 Answers0