消除联接3个表中的重复项 [英] Eliminate duplicates in Join over 3 Tables

问题描述

我正在使用以下select语句在网站上的网格中填充已输入3个表格的列表.

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.

我刚刚实现了Junction表，因为任何列表都可以具有多个类别，但是当我遇到问题时(使用CategoryY& amp; Z的ListingX)将出现在CatX&的列表中两次. CatY是因为在联结表上此特定列表有2个条目，所以我理解为什么它会出现两次，但是我希望所有内容只显示一次，而不是每个类别一次.

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

我认识到在这里使用distinct是行不通的，因为它是从联结表中拉出的第一个请求，也许查询的组织方式有所不同?

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?

推荐答案

因为对查询的每个输出列都应用了distinct.
City，Region，Country和Company在什么表中?如果它们在ListingJunction' or ListingCategories`中， 如果您需要清单和类别的独特列表，

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' orListingCategories`, 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

如果它们在"BND_Listing"中，请尝试以下操作:

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

这篇关于消除联接3个表中的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！