How to select unique values ​​from two related tables?

Question

MySQL

Hello.
Multilingual Laravel site two tables categories and category_descriptions
Structure

CREATE TABLE `blog_categories` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `parent_id` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `show_menu` enum('Y','N') COLLATE utf8mb4_unicode_ci NOT NULL,
  `show` enum('Y','N') COLLATE utf8mb4_unicode_ci NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `blog_category_descriptions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `lang` char(3) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category_id` bigint(20) UNSIGNED NOT NULL,
  `name` char(220) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `keywords` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `text` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug` varchar(220) COLLATE utf8mb4_unicode_ci NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Linked by key id = category_id
Here it is not possible to select the value of the category and the parent category from the database.
I tried to use Eloquent something like

$results = $this->startConditions()
            ->select($columns)
            ->with([
                'blogCategory:id,parent_id,show,show_menu,created_at'
            ])
            ->take($count)
            ->get();

And raw data

$rows =DB::select('SELECT bc.id, 
                                        bc.parent_id, 
                                        bc.show_menu, 
                                        bc.show, 
                                        bc.created_at, 
                                        bc.updated_at,
                                        bcd.lang AS lang_bcd,
                                        bcd.category_id,
                                        bcd2.category_id AS cat_id_bcd2,
                                        bcd.name AS name_bcd,
                                        bcd2.name AS name_bcd2
                                FROM blog_categories bc 
                                LEFT JOIN blog_category_descriptions bcd ON (bcd.category_id = bc.id)
                                LEFT JOIN blog_category_descriptions bcd2 ON (bcd2.category_id = bc.parent_id)
                                ORDER BY bc.created_at 
                                DESC limit ?', [$count]);

with raw, everything is selected, however, duplicates are obtained.
Can you suggest the correct way of such sampling or where to dig. Or how to select unique raw data.
Thanks

Answer 1

Duplicates can happen only in one case: if there is more than one entry in blog_category_descriptions with the same category_id.
You join tables with a one-to-many relationship, and join twice, respectively, joins by id will be multiplied with joins by parent id. If you have two entries in descriptions for id and parent id, then you will get four entries in the output. Exit to do through union all. Join the first table by id, then union all and join the same table by parent id.

Answer 2

distinct doesn't work there?