Subquery with multiple fields not working

Question

MySQL

I decided to try to do with one request what I usually did with two, but something swears at MySQL.
There are 3 tables.

You need to get com_dvr (dvr) and related sellers.
But as soon as I decided to try to see if it would work like this

SELECT s.*, (SELECT dwr.title as dvr_title FROM com_dvr AS dwr) AS sellers FROM (`com_dvr_sellers` AS s)

And it immediately tells me that the subquery returns more than one row, but I need this, I expected that it would turn out to be a multidimensional array

Answer 1

Given your addition, the query needs to be slightly modified:

SELECT seller.title seller_title, rel.price price, dvr.title dvr_title,dvr.id dvr_id FROM com_dvr dvr
LEFT JOIN com_dvr_relations rel ON dvr.id = rel.item_id
LEFT JOIN com_dvr_sellers seller ON rel.seller_id = seller.id;
ORDER BY dvr_title, dvr.id, seller.title, rel.price

And process the result like this (I write in PHP-like pseudocode):

$mysqli = new mysqli("example.com", "user", "password", "database");
(проверка на ошибку запроса)
$res = $mysqli->real_query($sql);
$mysqli->store_result();

// Получаем результат
$result = array();
while ($row = $res->fetch_assoc()) 
{
    $dvrId = $row[  'dvr_id' ];
    if ( !isset($result[$dvrId] )
    {
        $result[ $dvrId ] = array();
        $result[ $dvrId ][ 'dvr_title' ] = $row[ 'dvr_title' ];
        $result[ $dvrId ][ 'sellers' ] = array();
    }

    $result[ $dvrId ][ 'sellers' ][] = array(
        'seller_title' => $row[ 'seller_title' ]
       , 'seller_price' => $row['price']
    );
}

echo "<pre>";
var_dump( $result );

Answer 2

you wrote some very strange construction,
apparently you need something like this

SELECT 
    dvr.* ,
    sellers.*
FROM
    com_dvr as dvr
    JOIN com_dvr_relations as rel ON (dvr.id = rel.item_id)
    JOIN com_dvr_sellers as sellers ON (rel.item_id = sellers)

Answer 3

If I understand the task correctly, then here:
SELECT sell.* FROM com_dvr dvr
LEFT JOIN com_dvr_relations rel ON dvr.id = rel.item_id
LEFT JOIN com_dvr_sellers sell ON rel.seller_id = sell.id;