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.
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?
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments