MySQL Forums
Forum List  »  Stored Procedures

MySQL Syntax assistance
Posted by: Fraser Mathieson
Date: February 04, 2021 09:39AM

Hi

I have an extensive MsSQL database that I am migrating into MySQL. Most of the syntax differences between the two systems I am happy with, but I have the follow routine that the 'format' is used in several others.

I am struggling to get the correct syntax so can anyone assist? I have included column, table names just for reference with what is used.

I guess the main bit here is that using the for XML, which essentially pulls together a list from another table, based on a given table holding a relationship list.

Thanks

Current MySQL create code:

CREATE PROCEDURE `Advert_GetByID`(in _AdvertID bigint)
begin

SELECT
m.AdvertID,
(select cast(m2c.MagazineCategoryID as nvarchar(3)) + ',' as 'data()'
from Advert2Categories m2c
left join Advert m2 on m2c.MagazineCategoryID=m2.AdvertCategoryID where m.AdvertID=m2c.AdvertID for xml PATH('')) as 'AdvertCategoryID',
(select cast(r.RelatedArticleID as nvarchar(3)) + ',' as 'data()'
from AdvertRelatedArticles r
left join Advert m3 on r.RelatedArticleID=m3.AdvertID where m.AdvertID=r.ArticleID for xml PATH('')) as 'RelatedArticles',
(select cast(c.CountryID as nvarchar(3)) + ',' as 'data()' from Advert2Countries c
left join Advert m4 on c.AdvertID=m4.AdvertID where m.AdvertID=c.AdvertID for xml path('')) as 'Countries',
m.AdvertStartDate,
m.AdvertExpireDate,
m.AdvertTitle,
m.AdvertDescription,
m.AdvertBody,
m.AdvertStatus,
m.AdvertImageFileName,
m.YouTubeVideo,
m.FriendlyURL,
CAST(1 as bit) as 'UseImages',
m.LanguageID,
(select mc.MagazineCatName + ',' as 'data()'
from Advert2Categories m2c
left join Advert m2 on m2c.MagazineCategoryID=m2.AdvertCategoryID
left join MagazineCategories mc on m2c.MagazineCategoryID=mc.MagazineCatID
where m.AdvertID=m2c.AdvertID for xml PATH('')) as 'MagCat',
m.TimesRead,
m.Featured,
m.Lat,
m.Lng,
sau.FirstName,
sau.Surname,
sau.Username as 'Email',
m.MiigenAdId,
m.Paid,
m.PaidDate,
m.PaidAmount,
m.BusinessAccountId,
m.AdvertType,
(
select cast(m2g.AccountUserGroupsID as nvarchar(3)) + ',' as 'data()'
from Advert2Groups m2g
left join Advert m2 on m2g.AdvertID=m2.AdvertID
where m.AdvertID=m2g.AdvertID
for xml PATH('')
) as 'AdvertGroupID'
FROM
Advert m
left join Social_AccountUsers sau on m.CreatorID=sau.ID
where
m.AdvertID=_AdvertID
ORDER BY
m.AdvertStartDate DESC, m.AdvertID DESC;
end

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL Syntax assistance
139
February 04, 2021 09:39AM
56
February 04, 2021 12:56PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.