I'm using the following select statement to populate a grid on my website with listings that have been entered into 3 tables.
BND_Listing = Main Data
BND_ListingCategories = My Category data for the Listing Table
BND_LingingJunction = Junction Table for multi-category listings.
I've just implemented the Junction table as any listing can have more than 1 Category but my problem arrises when I have (ListingX that is using CategoryY & CategoryZ) will show up on my listing twice for CatX & CatY that's because there are 2 entries for this particular listing on my junction table so I understand why it's showing up twice but I'd like everything to show up just once rather than once per each category.
SELECT DISTINCT * FROM BND_ListingJunction
JOIN BND_listing on BND_listing.LID = BND_ListingJunction.Junc_LID
JOIN BND_ListingCategories
on BND_ListingCategories.CatID =
BND_ListingJunction.Junc_CatID
WHERE (CategoryName = '[querystring:filter-Category]' or
'[querystring:filter-Category]'='All')
and (City = '[querystring:filter-City]' or
'[querystring:filter-City]'='All')
and (Region= '[querystring:filter-State]' or
'[querystring:filter-State]'='All')
and (Country= '[querystring:filter-Country]' or
'[querystring:filter-Country]'='All')
and isnull(Company,'') <> ''
ORDER by Company ASC
I realize that using distinct is not working here because it's 1st pulling from my junction table perhaps the query needs be be organized differently?
Because distinct is applied to every output column from the query.
What table are City
, Region
, Country
and Company
in ? If they are in ListingJunction' or
ListingCategories`,
If you need distinct list of Listings and Categories,
SELECT DISTINCT l.*, c.CategoryName
FROM BND_Listing l
Join BND_ListingJunction j
on j.Junc_LID = l.LID
join BND_ListingCategories c
on c.CatId = j.Junc_CatID
Where (c.CategoryName = '[querystring:filter-Category]' or
'[querystring:filter-Category]'='All')
and (City = '[querystring:filter-City]' or
'[querystring:filter-City]'='All')
and (Region= '[querystring:filter-State]' or
'[querystring:filter-State]'='All')
and (Country= '[querystring:filter-Country]' or
'[querystring:filter-Country]'='All')
and isnull(Company,'') <> ''
Order by l.Company, l.LID
If all you want is distinct list of Listings try this:
SELECT DISTINCT * FROM BND_Listing l
Where Exists
(Select * from BND_ListingJunction j
join BND_ListingCategories c
on c.CatId = j.Junc_CatID
Where j.Junc_LID = l.LID
and (c.CategoryName = '[querystring:filter-Category]' or
'[querystring:filter-Category]'='All')
and (City = '[querystring:filter-City]' or
'[querystring:filter-City]'='All')
and (Region= '[querystring:filter-State]' or
'[querystring:filter-State]'='All')
and (Country= '[querystring:filter-Country]' or
'[querystring:filter-Country]'='All')
and isnull(Company,'') <> '')
ORDER by l.Company ASC
If they are in `BND_Listing', try this:
SELECT DISTINCT * FROM BND_Listing l
Where Exists
(Select * from BND_ListingJunction j
join BND_ListingCategories c
on c.CatId = j.Junc_CatID
Where j.Junc_LID = l.LID
and (c.CategoryName = '[querystring:filter-Category]' or
'[querystring:filter-Category]'='All'))
and (l.City = '[querystring:filter-City]' or
'[querystring:filter-City]'='All')
and (l.Region= '[querystring:filter-State]' or
'[querystring:filter-State]'='All')
and (l.Country= '[querystring:filter-Country]' or
'[querystring:filter-Country]'='All')
and isnull(l.Company,'') <> ''
ORDER by l.Company ASC