Can anyone tell me what this sql statement does?

[DavyAgten2]

SELECT pa.id_product_attribute as variant_id, p.id_product as product_id, replace(l.language_code,'nl', 'nl_BE') as 'locale'

FROM ps_product p

inner JOIN ps_product_lang pl ON ( p.id_product = pl.id_product )

inner JOIN ps_product_attribute pa ON ( p.id_product = pa.id_product )

inner JOIN ps_lang l ON (pl.id_lang = l.id_lang)

WHERE l.language_code in('nl')

and p.active=1

-- and pa.active=1

 

UNION

 

 

SELECT concat('dv_',cp.id_product) as variant_id,cp.id_product as product_id, 'nl_BE' as 'locale'

FROM

ps_category_product cp

inner join ps_category c on (c.id_category = cp.id_category)

inner join ps_category_lang cl on (cp.id_category = cl.id_category)

inner join ps_product p on (cp.id_product = p.id_product)

 

where

id_lang=6

and c.active = true

and p.active = 1

 

and not exists (

select child_cp.id_category

from ps_category_product child_cp

inner join ps_category child_c on ( child_cp.id_category = child_c.id_category)

inner join ps_product child_p on ( child_cp.id_product = child_p.id_product)

where

child_cp.id_product = cp.id_product

and child_c.nleft > c.nleft

and child_c.nright < c.nright

and child_c.active=true

and child_p.active=1)

 

group by variant_id

 

 

ORDER by product_id, variant_id

[bellini13]

it would seem to select active products and its active categories, using the Dutch language for any translatable fields

[DavyAgten2]

Thank you

 

I'm especially interested in the second part. After the union. And to be more specific the "and not exists" part. What does that mean?

[bellini13]

i'm not entirely sure, but I'll guess that it is removing duplicate products if they exist in more than 1 category.

[DavyAgten2]

thx