mysql return result from table related to other tables

Question

I'm trying to show result from table that related to many tables but my problem is that the query is return only one product that is on special what I want to return are both products products on special and also products that are not. My Php Query is :

$query = "SELECT DISTINCT p.product_id, p.price ,sp.date_end, f.percentage AS special_percentage , p.model, pd.name AS title, pd.description AS text, cd.name AS section, p.image, pd.tag, p.date_added AS created "
                ."FROM #__mijoshop_product AS p "
                ."JOIN #__mijoshop_product_special AS sp ON  p.product_id = sp.product_id "
                ."JOIN #__mijoshop_flordeco_product_special_percentage AS f ON sp.product_id = f.product_id "
                ."INNER JOIN #__mijoshop_product_description AS pd ON p.product_id = pd.product_id "
                ."LEFT JOIN #__mijoshop_product_to_store AS ps ON p.product_id = ps.product_id "
                ."LEFT JOIN #__mijoshop_product_to_category AS pc ON p.product_id = pc.product_id "
                ."LEFT JOIN #__mijoshop_category_description AS cd ON (pc.category_id = cd.category_id AND cd.language_id = {$language_id}) "
                ."LEFT JOIN #__mijoshop_category_to_store AS cs ON (pc.category_id = cs.category_id AND cs.store_id = {$store_id}) "
                ."WHERE (LOWER(pd.name) LIKE '%" . $search_text . "%' OR
                        LOWER(pd.description) LIKE '%" . $search_text . "%' OR 
                        LOWER(p.sku) LIKE '%" . $search_text . "%' OR ";

                        if( $model ) {
                            $query .= "LOWER(p.model) LIKE '%" . $search_text . "%' OR ";
                        }

                        $query .= "LOWER(pd.tag) LIKE '%" . $search_text . "%') "
                ."AND p.status = '1' "
                ."AND date(sp.date_end) >= date(NOW()) "
                ."AND p.date_available <= NOW() "
                ."AND ps.store_id = {$store_id} "
                ."AND pd.language_id = '" . $language_id . "' "
                ."GROUP BY p.product_id "
                ."ORDER BY {$order_by} "
                ."LIMIT ".$limit;

        $db->setQuery($query);
        $results = $db->loadObjectList();

Answer 1

Change the joins with #__mijoshop_product_special and #__mijoshop_flordeco_product_special_percentage to LEFT JOIN so that it won't restrict the results only to products that have matches in this table.

Also, you don't need to use SELECT DISTINCT when you use GROUP BY p.product_id; since there's only 1 row for each product ID, you can't get any duplicates. However, it also doesn't make sense to use GROUP BY when you're not using any aggregation functions, like SUM() or COUNT(). If all these tables are 1-to-1 correspondences, you shouldn't get any duplicates that need to be removed with either option.