Oracle query: how can I elimitate multiple results from this query?

I have the following tables:

CLIENT
 - ID
 - NAME
 - USER

ORDER
 - ID
 - DATE
 - CLIENT_ID
 - USER

ORDER_LINE
 - ORDER_ID
 - LINE_ID
 - USER

The user field tracks the person who last modified the record. Additionally, each line item may be modified by a different user.

I'm trying to find out, given a USER, what orders they have been involved with.

select 
 client.id,
 client.name,
 order.id,
 order.date,
 case
   when order_line.user = :user then 'Line Item'
   when order.user = :user then 'Order'
   when client.user = :user then 'Client'
 end
from
 order_line
 join order on order_id = order.id
 joi开发者_如何学Cn client on order.client_id = client.id
where
 order_line.user = :user
 or order.user = :user
 or client.user = :user

The problem is that a user may touch both the order and order_line tables, so I get multiple results. Adding select distinct doesn't help, since the result of the case statement is different in each case.

Is there a way to restructure the query so I only get one result per order?

The result of the case statement isn't really that important - it would be nice if I could get "Line Item, Order" in the case of someone touching both tables but just "Line Item" would be OK.

---

I propose this

It will show all orders that the user altered and if he altered line items but not what line items

SELECT   client.id,
         client.name,
         order.id,
         order.date
  FROM      order
         JOIN
            client
         ON client_id = client.id
 WHERE      order.USER = :USER
         OR client.USER = :USER
         OR :USER IN (SELECT   USER
                       FROM   ORDER_LINE OL
                      WHERE   ORDER_LINE.ORDER_ID = ORDER.ID)

---

does oracle have group_concat (or similar) as mysql has?

select client_id, name, order_id, date, group_concat(what separator ', ') as changes
from (
select 
 client.id as client_id,
 client.name,
 order.id as order_id,
 order.date,
 case
   when order_line.user = :user then 'Line Item'
   when order.user = :user then 'Order'
   when client.user = :user then 'Client'
 end as what
from
 order_line
 join order on order_id = order.id
 join client on order.client_id = client.id
where
 order_line.user = :user
 or order.user = :user
 or client.user = :user
) as part1
group by client_id, name, order_id, date;

---

How about concatenating the last column and using distinct, something like this:

select distinct
 client.id,
 client.name,
 order.id,
 order.date,
 case when order_line.user = :user then 'Line Item ' else '' end ||
  case when order.user = :user then 'Order ' else '' end ||
  case when client.user = :user then 'Client ' else '' end
from
 order_line
 join order on order_id = order.id
 join client on order.client_id = client.id
where
 order_line.user = :user
 or order.user = :user
 or client.user = :user

?

---

Try something like the following:

select client_id,
       max(client_name),
       order_id,
       max(order_date),
       max(Line_User),
       max(Order_User),
       max(Client_User)
(select client.id client_id,
        client.name client_name,
        order.id order_id,
        order.date order_date,
        case when order_line.user = :user then 'Y' else 'N' end Line_User,
        case when order.user = :user then 'Y' else 'N' end Order_User,
        case when client.user = :user then 'Y' else 'N' end Client_User
 from order_line
 join order on order_id = order.id
 join client on order.client_id = client.id
 where order_line.user = :user or order.user = :user or client.user = :user)
group by client_id, order_id;