记一个sql需求畅想

希望得到这样的一列数据,这一列中的每个数据结构如下:

data1{
    field1:value1,
    field2:value2,
    listField1:{
        grandsonField1:value3
        grandsonField2:value4
    }
}

简单说来就是每个元素内含一个一对多关系。

现在要查询这样的一列元素，考虑到分页需求，希望使用一次查询得到数据集。即：一条sql查询一组一对多数据,每条数据都包含一对多和一对一关系。

尝试了一下,sql如下:

SELECT
	dm_service.service_num,
	dm_trip.arrival_time,
	dm_trip.arrival_city,
	dm_trip.arrival_country,
	dm_trip.arrival_citycode,
	dm_trip.departure_time,
	dm_trip.departure_city,
	dm_trip.departure_country,
	dm_trip.departure_citycode,
	dm_trip.vehicle_type,
	count( dm_record.record_no ) AS countAll,
	count( finishRecord.record_no ) AS countDone,
	(
SELECT
	dm_peer.peer_nickname,
	count( recordtable.record_no ) AS countRecord,
	dm_peer.peer_contacttel,
	defaultAddress.receivername AS senderName,
	defaultAddress.receivertel AS senderTel,
	defaultAddress.city AS senderCity,
	defaultAddress.detailaddress AS senderDetailaddress,
	dm_address.receivername,
	dm_address.receivertel,
	dm_address.city,
	dm_address.detailaddress,
	dm_order.createtime 
FROM
	dm_record,
	dm_peer,
	dm_address,
	dm_order,
	( SELECT dm_record.record_no FROM dm_record ) AS recordtable,
	(
SELECT
	dm_address.receivername,
	dm_address.receivertel,
	dm_address.city,
	dm_address.detailaddress 
FROM
	dm_address,
	dm_record 
WHERE
	dm_address.peerid = dm_record.peer_id 
	AND dm_address.state = 0 
	) AS defaultAddress 
WHERE
	dm_record.service_id = 123 
	AND dm_record.peer_id = dm_peer.peer_id 
	AND dm_record.order_id = dm_order.order_id 
	AND dm_order.address_id = dm_address.id 
	) AS list 
FROM
	dm_service,
	dm_trip,
	dm_record,
	( SELECT dm_record.record_no FROM dm_record WHERE dm_record.state = 2 ) finishRecord 
WHERE
	dm_service.peerid = '231131' 
	AND dm_service.deletetime IS NULL 
	AND dm_service.state = 0 
	AND dm_service.trip_id = dm_trip.id 
	AND dm_record.service_id = dm_service.id

sql是错的,这点和预计的一样。

我想弄清楚的是，关系型数据库可以做到这种查询吗？