CODEWITHSUNDEEP
sqlsql-serverstored-procedurescountnested
user2571510
user2571510

Reputation: 11377

SQL Server: How to add count to nested select

I have two tables, one containing a list of files and one containing a list of tags which are linked by a fileID.

Currently I select this as follows which works fine so far.

How do I have to amend this if I want to count the tags per file and show this in addition to the selected data ? What I want to do is show how many tags are assigned to each file.

My SP: 

SELECT      C.fileTitle,
            C.fileID,
            (
                SELECT      T.fileTag
                FROM        Files_Tags T
                WHERE       T.fileID = C.fileID
                ORDER BY    T.fileTag
                FOR XML     PATH(''), ELEMENTS, TYPE
            ) AS tags
FROM        Files C
ORDER BY    C.fileTitle
FOR XML PATH('files'), ELEMENTS, TYPE, ROOT('root')

Many thanks for any help with this, Tim.

Upvotes: 0

Views: 100

Answers (2)

Arion
Arion

Reputation: 31239

Can't you just do this?:

SELECT      C.fileTitle,
            C.fileID,
            (
                SELECT      T.fileTag
                FROM        Files_Tags T
                WHERE       T.fileID = C.fileID
                ORDER BY    T.fileTag
                FOR XML     PATH(''), ELEMENTS, TYPE
            ) AS tags,
            (
                SELECT
                    COUNT(*)
                FROM
                    Files_Tags T
                WHERE
                    T.fileID = C.fileID
            ) AS NumberOfTages
FROM        Files C
ORDER BY    C.fileTitle
FOR XML PATH('files'), ELEMENTS, TYPE, ROOT('root')

Anding a sub query for the count

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can add a subquery:

SELECT      C.fileTitle,
            C.fileID,
            (
                SELECT      COUNT(*)
                FROM        Files_Tags T
                WHERE       T.fileID = C.fileID
            ) AS NumTags,
            (
                SELECT      T.fileTag
                FROM        Files_Tags T
                WHERE       T.fileID = C.fileID
                ORDER BY    T.fileTag
                FOR XML     PATH(''), ELEMENTS, TYPE
            ) AS tags

You could also put in a join and aggregation in the outer query. But, your query already has to use a nested select for the concatenation, so you might as well use the same structure for the count.

Upvotes: 1

Related Questions