Query parent og child record and return parent record
<p>I'm working on a product database (PDB) to be used both for a webshop and as articles in our ERP-system. Most of the data comes directly from our suppliers as CSV-files, and we've added options to override certain fields, which has resulted in a table structure I can't figure out how to efficiently query.</p>

<p>In essence the table structure consists of two tables, article and extended_article, where the last table contains a field called parent_article_code used to combine women articles into men for display purposes. Not all articles contain extended_articles and not all articles with extended_articles has a value in parent_article_code.</p>

<p>I need to perform a query returning all articles with a match, but only show the parent article where either the parent or child matches the query. This is a really simplefied version of the tables and I'll also add a query to show how the data is actually structured.</p>

<p>article table:</p>

<pre><code>    -------------------------------------------------------------------------
    |  id  |  code    |  name          |  description                       |
    |  1   |  029320  |  T-shirt       |  Nice t-shirt with awsome logo     |
    |  2   |  029321  |  T-shirt Lady  |  Nice t-shirt with awsome logo     |
    |  3   |  029322  |  Sweatshirt    |  Nice sweatshirt with awsome logo  |
    |  4   |  029323  |  Pants         |  Nice pants with awsome logo       |
    -------------------------------------------------------------------------
</code></pre>

<p>extended_article table:</p>

<pre><code>    -------------------------------------------------------------------------------------------------
    |  id  |  article_code  |  parent_article_code  |  name_override       |  description_override  |
    |  1   |  029320        |                       |  T-shirt with print  |                        |
    |  2   |  029321        |  029320               |                      |                        |
    |  3   |  029323        |                       |  Awsome pants!       |                        |
    -------------------------------------------------------------------------------------------------
</code></pre>

<p>Example SQL query (returning all records with match - not only children):</p>

<pre><code>SELECT article.*, 
    article.id, 
    extended_article.parent_article_code, 
    extended_article.impressions, 
    extended_article.is_base_assortment, 
    extended_article.is_visible, 
    extended_article.name_override, 
    extended_article.description_override, 
    extended_article.additional_description, 
    extended_article.preferred_image_file_name, 
    supplier.name AS supplier_name, 
    brand.name AS brand_name
FROM article 
LEFT OUTER JOIN article_sort_order 
ON article_sort_order.article_code = article.code AND category_id IS NULL 
LEFT OUTER JOIN extended_article 
ON extended_article.article_code = article.code 
LEFT JOIN supplier 
ON supplier.id = article.supplier_id 
LEFT JOIN brand 
ON brand.id = article.brand_id  
WHERE (extended_article.is_base_assortment = 1) 
AND  (article.is_expired = 0) 
AND (extended_article.is_visible = 1) 
AND ((EXISTS 
(
    SELECT 1 
    FROM article_color_groups 
    WHERE article_color_groups.article_code = article.code 
    AND article_color_groups.color_group_id = 4
)
))
</code></pre>

<p>So my question is this:
How can I efficently query this table structure and have a result set that only contains the parent articles?</p>

<p>EDIT:</p>

<p>I had an idea to move the query part into two subqueries selecting only IDs and using UNION to generate a list to select the appropriate fields from. This seems to work, but might not be the most efficient solution?</p>

<pre><code>SELECT article.*, article.id, extended_article.parent_article_code, extended_article.impressions, extended_article.is_base_assortment, extended_article.is_visible, extended_article.name_override, extended_article.description_override, extended_article.additional_description, extended_article.preferred_image_file_name, supplier.name AS supplier_name, brand.name AS brand_name
FROM article
LEFT OUTER JOIN article_sort_order ON article_sort_order.article_code = article.code AND category_id IS NULL
LEFT OUTER JOIN extended_article ON extended_article.article_code = article.code
LEFT JOIN supplier ON supplier.id = article.supplier_id
LEFT JOIN brand ON brand.id = article.brand_id
WHERE article.code IN (
                SELECT article.code
                FROM article
                LEFT OUTER JOIN article_sort_order ON article_sort_order.article_code = article.code AND category_id IS NULL
                LEFT OUTER JOIN extended_article ON extended_article.article_code = article.code
                LEFT JOIN supplier ON supplier.id = article.supplier_id
                LEFT JOIN brand ON brand.id = article.brand_id
                WHERE   (NULLIF(extended_article.parent_article_code, '') IS NULL)
                        AND
                        (extended_article.is_base_assortment = 1)
                        AND
                        (article.is_expired = 0)
                        AND
                        (extended_article.is_visible = 1)
                        AND
                        ((EXISTS (SELECT 1 FROM article_color_groups WHERE article_color_groups.article_code = article.code AND article_color_groups.color_group_id = 11)))

                UNION

                SELECT extended_article.parent_article_code
                FROM article
                LEFT OUTER JOIN article_sort_order ON article_sort_order.article_code = article.code AND category_id IS NULL
                LEFT OUTER JOIN extended_article ON extended_article.article_code = article.code
                LEFT JOIN supplier ON supplier.id = article.supplier_id
                LEFT JOIN brand ON brand.id = article.brand_id
                WHERE   (NULLIF(extended_article.parent_article_code, '') IS NOT NULL)
                        AND
                        (article.is_expired = 0)
                        AND
                        ((EXISTS (SELECT 1 FROM article_color_groups WHERE article_color_groups.article_code = article.code AND article_color_groups.color_group_id = 11)))
            )
ORDER BY -article_sort_order.sort_order DESC;
</code></pre>