URGENT + Need Database query to pull some content

Vote:
 

There has been a one time need for pulling some content with the criteria so looking for database query: 

I need to be able to query data based on a page type and with some other filters:

I have a page type as ContentArticle and need to query all Content Article pages, with data like, Name, id, expiration date, its parent link (type and Name)

#224021
Jun 09, 2020 14:24
Dileep D - Jun 09, 2020 18:27
I have this so far which gives me Id, name and link and parent id too, how do I add parent name and type as a column in this query. This is getting tricky as I need the parent hierarchy until Start page as part of this query.
Select
T.pkID 'Content Id',
TPL.Name 'Content Name',
T.fkParentID 'Parent Id',
TPL.LinkURL 'Link URL'
from tblPage T
inner join tblPageLanguage TPL ON T.pkID = TPL.fkPageID
inner join tblPageType TP
on T.fkPageTypeID = TP.pkID
where TP.Name = 'ContentArticlePage'
Vote:
 

I went through the articles and got an idea of what data stays in which table and came up with above query. However from above query, I cannot find parent page names and types all the way till root. That is what am trying to find.

I tried putting the data in temp tables and creating joins but with that I can get only upto one level up.

#224045
Jun 10, 2020 0:15
Vote:
 

Creating some temp tables and eventually joining on data I need, was able to get this resolved. However big thanks to the articles mentioned above which served as reference (understanding) which table to look for what kind of data.

#224078
Jun 10, 2020 18:04
Vote:
 

Hi Dileep

Glad those links helped and hope Khurram sees this post one day - thank you Khurram :)

David

#224081
Jun 10, 2020 18:22
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.