Sql query group and read one-CodePudding

I have two tables:

Client:

ID int
Name varchar

Orders:

ID int
ClientID int
OrderDetails

One client may has many orders.

How can I obtain dataset for all clients and only one order for this client?

Group by client and add to this group one order

Example:

Client:

ID| Name  |
-- ------- 
1 | John  |
2 | Peter |
3 | Andry |

Orders:

ID| ClientID | OrderDetails |
-- ---------- -------------- 
1 | 1        |  Done        |   
2 | 1        |  Progress    |
3 | 2        |  Progress    |
4 | 2        |  Done        |
5 | 2        |  Fail        |

Expected result:

ClientID | Name | OrderID | OrderDetails |
--------- ------ --------- -------------- 
    1    | John |   1     |   Done       |
    2    | Peter|   2     |   Progress   |
    3    | Andry|   None  |    None      |

CodePudding user response：

If which order doesn't matter, something like this would work:

select distinct on (c.id)
  c.id, c.name, o.id as order_id, o.order_details
from
  client c
  left join orders o on
    c.id = o.client_id
order by
  c.id

If you change your mind, and the order does matter, just change the order by to include the field that determines which order you pick.

Alternately, you can list all orders with something like this:

select
  c.id, c.name,
  array_agg (o.id) as order_ids,
  array_agg (o.order_details) as details
from
  client c
  left join orders o on
    c.id = o.client_id
group by
  c.id, c.name