How to find all the fields value of an item with sql query in sitecore?

Question

So here is a scenario is like:

find the fields value of all the items of specific parent template item in Sitecore (i.e. suppose i want to list fields value of newslist, where newslist is news type item)

with SQL Query so what i am doing with the help of SharedFields and Items table I am trying to get the information but unable to get the information. For single field I am getting but for multiple I am not able to do : Here is the query:

SELECT distinct S.ItemId, S.Value AS NewsType,

FORMAT(S.Created,'yyyy/MM/dd') AS CreatedOn, FORMAT(S.Updated,'yyyy/MM/dd') AS UpdatedOn

FROM 
[DBName].[dbo].[Items] I,
[DBName].[dbo].[SharedFields] S

WHERE I.ParentID='{XXXXXX-X-XXXXX-XXXXX-XXXXXX}'
AND S.FieldId='{YYYY-YYYY-Y-Y-Y-Y-Y-YYYYY}'

where PARENTID is the id of news item and fieldid is id of newstype

now i want to add one more column into the query as newsOf

So how can I do that?

Answer 1

You need to add another table to your FROM clause (second SharedFields) and use JOIN like this:

SELECT 
    S.ItemId, 
    S.Value AS NewsType, 
    S2.Value AS NewsOf,
    FORMAT(S.Created,'yyyy/MM/dd') AS CreatedOn, 
    FORMAT(S.Updated,'yyyy/MM/dd') AS UpdatedOn
FROM 
    [DBName].[dbo].[Items] I
    JOIN [DBName].[dbo].[SharedFields] S ON S.ItemId = I.ID
    JOIN [DBName].[dbo].[SharedFields] S2 ON S2.ItemId = I.ID
WHERE 
    I.ParentID='{11111111-1111-1111-1111-111111111111}'
    AND S.FieldId='{field-1-id}'
    AND S2.FieldId='{field-2-id}'