query fra mssql til mysql
Er der nogle der kan hjælpe mig med at omskrive disse så de virker i mysql1:
---
select author_firstname,author_lastname,author_biography,author_image_path,approved
,
(isnull(substring(OrdIdList, 1, datalength(OrdIdList)/2 - 2),'')) as bøger
from author
cross apply (select convert(nvarchar(100), product_sku)+' '+convert(nvarchar(100), product_name)+', ' from product join author_product on author.author_key = author_product.author_key where author_product.product_key = product.product_key for xml path('')) as Dummy(OrdIdList)
---
2:
---
select
product_sku,
product_name,
product_info,
(select property_value_text from product_properties where property_name = 'review' and product_key = product.product_key) as product_review,
(select property_value_text from product_properties where property_name = 'table_of_content' and product_key = product.product_key) as product_tableofcontent,
(isnull(substring(OrdIdList, 1, datalength(OrdIdList)/2 - 2),'')) as forfatter,
(select property_value_text from product_properties where property_name = 'keywords' and product_key = product.product_key) as product_keywords,
(select property_value_text from product_properties where property_name = 'numberofpages' and product_key = product.product_key) as product_numberofpages,
(select property_value_text from product_properties where property_name = 'publishedyear' and product_key = product.product_key) as product_publishedyear,
(select property_value_text from product_properties where property_name = 'format' and product_key = product.product_key) as product_format,
(select property_value_text from product_properties where property_name = 'binding_type' and product_key = product.product_key) as product_bindingtype,
(select property_value_text from product_properties where property_name = 'edition' and product_key = product.product_key) as product_edition,
product_weight,
(select category_name from product_category join category on product_category.category_key = category.category_key where category_level=1 and category_parent_key = 0 and product_category.product_key = product.product_key) as redaktion,
(select category_name from product_category join category on product_category.category_key = category.category_key where category_level=2 and product_category.product_key = product.product_key) as emne1,
(select category_name from product_category join category on product_category.category_key = category.category_key where category_level=3 and product_category.product_key = product.product_key) as emne2,
(select price_amount from product_price where price_key = 'RG' and product_key = product.product_key) as normalpris,
(select price_amount from product_price where price_key = 'SL' and product_key = product.product_key) as kampagne_pris,
(select price_start_date from product_price where price_key = 'SL' and product_key = product.product_key) as kampagne_start,
(select price_end_date from product_price where price_key = 'SL' and product_key = product.product_key) as kampagne_stop,
(select price_name from product_price join price on product_price.price_key = price.price_key where price.price_key <> 'RG' and product_key = product.product_key) as kampagne_type,
(select approved from product_price where product_key = product.product_key and price_key <> 'RG') as godkendt,
product_frontpage
from product
cross apply (select convert(nvarchar(100), author_firstname)+' '+convert(nvarchar(100), author_lastname)+', ' from author_product join author on author_product.author_key = author.author_key where author_product.product_key = product.product_key for xml path('')) as Dummy(OrdIdList)
where product.vendor_key=001
---