I have this table:

enter image description here

from which I create this View:

enter image description here

This view's CardId allows me to JOIN against the Card table with so I can retrieve the Count from any CardId. Here's my SQL:

        si.CardId SourceCardId,
        ti.CardId TargetCardId,
        (SELECT TOP 1 r.Name
        FROM dbo.Relationship r
        WHERE r.Id = rac.RelationshipId) [Name],
        Count(*) [Count]
    FROM dbo.Relationship_AcquiredCard rac
    JOIN dbo.AcquiredCard sac ON rac.SourceAcquiredCardId = sac.Id
    JOIN dbo.AcquiredCard tac ON rac.TargetAcquiredCardId = tac.Id
    JOIN dbo.CardInstance si ON sac.CardInstanceId = si.Id
    JOIN dbo.CardInstance ti ON tac.CardInstanceId = ti.Id
    GROUP BY si.CardId, ti.CardId, rac.RelationshipId
    -- you can probably ignore everything above
    ) X
    (values (X.TargetCardId),
    ) whatdoesthisdo(CardId) --wut

What does whatdoesthisdo do? I got the CROSS APPLY from this answer. If I try to alias in the usual way, I get this error:

enter image description here



It should be clear from the below

SELECT WhatDoesThisDo.CardId
               (2) ) WhatDoesThisDo(CardId) 

WhatDoesThisDo provides the table alias for the derived table defined by the VALUES clause. It then requires a comma delimited list of all column names (as there is no way of naming them inside the VALUES itself).

In this case it only returns one column so the complete column list is (CardId)

The relevant part of the grammar is

derived_table [ [ AS ] table_alias ] [ ( column_alias [ ,...n ] ) ] 

though the optionality indicated by the square brackets above does not apply with table value constructors.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.