Reputation: 137
I have following query .
select entity_id ,parent_id,name as label , url_key as name from magento_catalog_category_flat_store_1 where entity_id IN(
SELECT distinct T1.parent_id
FROM magento_catalog_category_flat_store_1 T1
INNER JOIN magento_catalog_category_flat_store_1 as T2 ON
T1.entity_id = T2.parent_id
RIGHT JOIN sohyper_region_activity as T3 on
T2.entity_id = T3.activity_id)
The above query decreases the performance because of IN clause . Can anyone tell me how to replace IN clause with JOIN in this query ?
Thanks.
Upvotes: 3
Views: 11886
Reputation: 4527
Not tested, obviously!
WITH (
SELECT distinct T1.parent_id
FROM magento_catalog_category_flat_store_1 T1
INNER JOIN magento_catalog_category_flat_store_1 as T2 ON
T1.entity_id = T2.parent_id
RIGHT JOIN sohyper_region_activity as T3 on
T2.entity_id = T3.activity_id)
) T0
select entity_id ,parent_id,name as label , url_key as name from
magento_catalog_category_flat_store_1 T1
INNER JOIN magento_catalog_category_flat_store_1 as T2 ON
T1.entity_id = T2.parent_id
RIGHT JOIN sohyper_region_activity as T3 on
T2.entity_id = T3.activity_id
INNER JOIN T0
ON T0.entity_id = magento_catalog_category_flat_store_1.entity_id
Upvotes: 1
Reputation: 782785
In general
SELECT ...
FROM Table1
WHERE col1 IN (SELECT col2 FROM ...)
can be converted to:
SELECT ...
FROM Table1 t1
JOIN (SELECT DISTINCT col2 FROM ...) t2
ON t1.col1 = t2.col2
Upvotes: 18
Reputation: 28423
Try like this
SELECT Tab1.entity_id ,Tab1.parent_id,Tab1.name As Label , Tab1.url_key As Name
FROM magento_catalog_category_flat_store_1 Tab1 JOIN
(
SELECT distinct T1.parent_id
FROM magento_catalog_category_flat_store_1 T1
INNER JOIN magento_catalog_category_flat_store_1 as T2 ON
T1.entity_id = T2.parent_id
RIGHT JOIN sohyper_region_activity as T3 on
T2.entity_id = T3.activity_id
) Tab2 ON Tab1.entity_id = Tab2.parent_id
Upvotes: 0