Query joining primary table with foreign table and the top 3 records as a html from a column of the foreign table

Question

I have two Tables: Category & Articles

Category Table:

CID |  CategoryName 
----+--------------
1   |  AAA Catg
2   |  BBB Catg
3   |  CCC Catg
4   |  DDD Catg
5   |  EEE Catg
6   |  FFF Catg

Articles Table:

AID | CID | ArticleTitle | ArticleURL      | Descrption      | Date
----+-----+--------------+-----------------+-----------------+-----------
1   | 1   | AA1 Title    | http://aa1.html | AA1 Description | 01-07-2017
2   | 1   | AA2 Title    | http://aa2.html | AA2 Description | 02-07-2017
3   | 1   | AA3 Title    | http://aa3.html | AA3 Description | 03-07-2017
4   | 1   | AA4 Title    | http://aa4.html | AA4 Description | 04-07-2017
5   | 1   | AA5 Title    | http://aa5.html | AA5 Description | 05-07-2017
6   | 2   | BB1 Title    | http://bb1.html | BB1 Description | 01-07-2017
7   | 2   | BB2 Title    | http://bb2.html | BB2 Description | 02-07-2017
8   | 2   | BB3 Title    | http://bb3.html | BB3 Description | 03-07-2017
9   | 3   | CC1 Title    | http://cc1.html | CC1 Description | 01-07-2017
10  | 3   | CC2 Title    | http://cc2.html | CC2 Description | 02-07-2017
11  | 4   | DD1 Title    | http://dd1.html | DD1 Description | 01-07-2017

I need the resulting table by joining Category table with Articles table to having a column which will generate html by fetching top 3 records of Articles table for each category.
The column html should be like:

<li>
    <a href="http://aa1.html">aa1 Title</a>
    <p>AA1 Description</p>
</li>
<li>
    <a href="http://aa2.html">AA2 Title</a>
</li>
<li>
    <a href="http://aa3.html">AA3 Title</a>
</li>

Where the first <li> will have an anchor tag and a paragraph tag will be at the first article

<a href="http://aa1.html">aa1 Title</a>
<p>AA1 Description</p>

And the rest of the two <li> tags will have only anchor tag will be the next 2 articles

<a href="http://aa2.html">AA2 Title</a>

Below is the resulting table

CID | Category | HTML
----+----------+---------------------------------------------------------
1   | AAA Catg | <li><a href="http://aa1.html">AA1 Title</a><p>AA1 Description</p></li>
    |          | <li><a href="http://aa2.html">AA2 Title</a></li>
    |          | <li><a href="http://aa3.html">AA3 Title</a></li>

2   | BBB Catg | <li><a href="http://bb1.html">BB1 Title</a><p>BB1 Description</p></li>
    |          | <li><a href="http://bb2.html">BB2 Title</a></li>
    |          | <li><a href="http://bb3.html">BB3 Title</a></li>

3   | CCC Catg | <li><a href="http://cc1.html">CC1 Title</a><p>CC1 Description</p></li>
    |          | <li><a href="http://cc2.html">CC2 Title</a></li>

4   | DDD Catg | <li><a href="http://dd1.html">DD1 Title</a><p>DD1 Description</p></li>

5   | EEE Catg | 

6   | FFF Catg |

This is the query I used to

SELECT * FROM
(
    SELECT DISTINCT [CID]
    FROM [Articles]
) A
CROSS APPLY
(
    SELECT TOP 3 '<li><a href="' + B.[ArticleURL] + '">' + B.[ArticleTitle] + '</a><p>' + B.[Description] + '</p></li>'
    FROM [Articles] AS B 
    WHERE A.[CID] = B.[CID]
    FOR XML PATH('')
) C (ArticleList)

The above query will generate html with paragraph in all li tags

<li>
    <a href="http://aa1.html">aa1 Title</a>
    <p>AA1 Description</p>
</li>
<li>
    <a href="http://aa2.html">AA2 Title</a>
    <p>AA2 Description</p>
</li>
<li>
    <a href="http://aa3.html">AA3 Title</a>
    <p>AA3 Description</p>
</li>

But I need the paragraph tag only within 1st li tag, not all 3. And also the html tags will be in the form

&lt; li&gt;
        &lt; a href="http://aa3.html"&gt;AA3 Title&lt; /a&gt;
        &lt; p&gt;AA3 Description&lt; /p&gt;
&lt;/li&gt;

How to get it in the html format?

If you don't need the HTML in the database itself, but intend to export it, consider exporting your data instead and using a template engine such as Apache Velocity (for Java) or Jinja2 (for Python) to construct your HTML. — rd_nielsen, Jul 22 '17 at 13:22

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Try the query below

 ;with testdata as (
   select Articles.CID, CategoryName, ArticleTitle , ArticleURL,Description,Date
   ,row_number() over(partition by Category.CID order by AID) as rowNumber
   from Category inner join Articles on Category.CID= Articles.CID
 )
select 
  case when rowNumber=1 then '<li>' else '' End + '<a href="' 
  + [ArticleURL] + '">' + [ArticleTitle] + '</a><p>' + [Description] 
  + '</p>' +  case when rowNumber=3 then '</li>' else '' End 
from testdata where rowNumber<=3
for xml path('') ;

EDIT2 Using "For XML"

select CategoryName as '@id', (SELECT top 3
        ArticleURL as '@href'
       , ArticleTITLE   as'p'
    FROM
        Articles sub
    WHERE
        sub.CID= Category.CID
    order by sub.AID
    FOR 
        XML path('a'), 
        type
 )
   
 from Category 

for XML path('li')

I also added new query using "For XML" – Eid Morsy Jul 30 '17 at 09:22 — Eid Morsy, Jul 30 '17 at 09:22

shA.t · Accepted Answer · 2017-08-21T15:50:17.083

I think you can use this query:

;with t as (
    select 
        *, 
        row_number() over (partition by CID order by AID) seq
    --  ^ this field will help us to find first Article of each Category
    from
        Articles
)
select 
    *,
    replace(replace(    -- I use `replace` for changing `<`, `>` to `&lt;`, ``&gt;`. `
    (select top 3
        ArticleURL "a/@href",
     --             ^ with using this column naming you will have a tag of `a` with attribute `href`. 
        case when seq = 1 then ArticleTitle end p
     from t
     where Category.CID = t.CID
     for xml path ('li')
     ) , '<', '&lt;'), '>', '&gt;') HTML
from 
    Category;

[ SQL Fiddle Demo ]
[ SQL Fiddle Demo - original tags ]

Query joining primary table with foreign table and the top 3 records as a html from a column of the foreign table

2 Answers2

EDIT2 Using "For XML"