SQL Query returns multiple rows of the same record when View includes one-to-many table

Question

In MS-SQL, I have a View 'ListingResult' which contains rows from tables 'ListingCategoryXref' and 'Listing'. This is the View statement:

SELECT        
    dbo.Listing.ListingName,  
    dbo.Listing.ListingId,  
    dbo.ListingCategoryXref.CategoryId
FROM    dbo.Listing INNER JOIN
        dbo.ListingCategoryXref ON dbo.Listing.ListingId = dbo.ListingCategoryXref.ListingId

GROUP BY 
    dbo.Listing.ListingName, 
    dbo.Listing.ListingId, 
        dbo.ListingCategoryXref.CategoryId

Listings can have many rows in ListingCategoryXref, thus.

ListingResult (View)
Listing (table)
ListingId   ListingName     StateId
1           Toms bar        3
2           French place    5

ListingCategoryXref (table)
ListingId   CategoryId
1           10
1           15

The query below returns a row per Listing per ListingCategoryXref.

SELECT TOP(26) 
        [ListingResult].[ListingId],
        [ListingResult].[ListingName]
FROM    [ListingResult]     
WHERE   [ListingResult].[StateId] = 3

So 'Tom's Bar' is returned twice as it has two categories. I figure I can either change the query above, or change the ListingResult View in SQL. I still need to return 26 results which I can't dictate if I use a wrapped select statement with ROW_NUMBER() OVER(PARTITION BY ListingId. (Is that true?) I'm using LLBLGen to access the DB so I'd prefer to change the view, if that is possible? Apologies for my newness to SQL being that obvious.

Answer 1

From the query above, the following result will be returned...

ListingName | ListingId | CategoryId
Toms bar | 1 | 10
Toms bar |1 | 15

If you only want Toms bar to be returned once, you'll need to remove the CategoryId column from the result set, and the group by clause, or add CategoryId to an agrgate function, and remove it from the group by clause i.e.

SELECT
    dbo.Listing.ListingName,
    dbo.Listing.ListingId,
    COUNT(dbo.ListingCategoryXref.CategoryId) as Categories
FROM dbo.Listing 
    INNER JOIN dbo.ListingCategoryXref ON dbo.Listing.ListingId = dbo.ListingCategoryXref.ListingId
GROUP BY dbo.Listing.ListingName, dbo.Listing.ListingId

Which will return...

ListingName | ListingId | Categories
Toms bar | 1 | 2

Can you give an example of what you would like to see?