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

Viewed 52

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?

2 Answers
Related