Here's an SQL query to extract data from four datasets:

• SPA items (crm_dynamic_items_1052)
• SPA products (crm_dynamic_items_prod_1052)
• Product properties (crm_product_property)
• Product property values (crm_product_property_value)

The query selects SPA items that have products with "coat" in their names and displays their properties by color. You can copy it and see how it works. Before running the query, replace crm_dynamic_items_"type ID" and crm_dynamic_items_prod_"type ID" with your SPA.

SELECT 
    crm_dynamic_items_1052.ID AS "SPA item ID",
    crm_dynamic_items_1052.TITLE AS "SPA item name",
    crm_dynamic_items_prod_1052.PRODUCT_ID AS "Product ID",
    crm_dynamic_items_prod_1052.PRODUCT_NAME AS "Product name",
    crm_dynamic_items_prod_1052.PRICE AS "Product price",
    crm_product_property.NAME AS "Property name",
    crm_product_property_value.VALUE AS "Property value"
FROM
    crm_dynamic_items_1052
LEFT JOIN 
    crm_dynamic_items_prod_1052 ON crm_dynamic_items_prod_1052.ITEM_ID = crm_dynamic_items_1052.ID
LEFT JOIN 
    crm_product_property_value ON crm_product_property_value.PRODUCT_ID = crm_dynamic_items_prod_1052.PRODUCT_ID
LEFT JOIN 
    crm_product_property ON crm_product_property.ID = crm_product_property_value.PROPERTY_ID
WHERE 
    crm_dynamic_items_prod_1052.PRODUCT_NAME LIKE '%coat%'
    AND crm_product_property.NAME = 'Color';

SELECT: Chooses which data to extract. For example, the SELECT crm_dynamic_items_1052.ID AS "SPA item ID" part does the following:

• Selects the SPA item ID from the crm_dynamic_items_1052 dataset
• Assigns it the name SPA item ID
• Displays the result in a separate column

FROM: Specifies the main table for the query, such as the crm_dynamic_items_1052 table containing information on the SPA items.

LEFT JOIN: Connects crm_dynamic_items_1036 to crm_dynamic_items_prod_1052 by the SPA item ID. The query connects each SPA item with its products. Use LEFT JOIN to ensure no SPA items are missed, even if they don't have linked products or properties.

LEFT JOIN: Connects crm_dynamic_items_prod_1052 to crm_product_property_value by product ID. The query connects each product with property values.

LEFT JOIN: Connects the crm_product_property_value table to crm_product_property by the product property ID. The query connects the property values with their names.

WHERE: Filters the query results to show only the SPA items that have products with "coat" in their names, showing their properties by color. You can delete or change the filters in the query. For example, in the condition crm_dynamic_items_prod_1052.PRODUCT_NAME LIKE '%coat%', you can replace "coat" to search for different products.