选择带有JOIN的DISTINCT [英] SELECT DISTINCT with JOIN

问题描述

大家好


有问题我会非常感谢帮助。

我有一个标准格式的3张桌子，例如


产品

类别

类别_产品


后者允许我有多个产品类别。


除了一个烦人的小东西外，它的效果很好。


当一个单独的产品（在一个以上的topcategory中）是将

添加到它显示两次的购物车中，因为在我的选择声明中我已经列出了类别
。我意识到我可以从

语句中删除TopCategory，这使得我的DISTINCT按照我的意愿工作，但我更喜欢将TopCategory设为

，因为它可以节省我以后必须另一个SQL查询（我已经

做一个允许我不在列表中列出类别....但如果我

可以克服这一个...那么我也可以删除它。


这是我的表结构（必要的位）

产品

idProduct int

....


类别

idcategory int

idParentCategory int

topcategory int

...


categories_products

idCatProd int

idProduct int

idCategory


当我运行查询，例如


SELECT DISTINCT a.idProduct，a.description，a.descriptionLong ，

a.listPrice，a.price，a.smallImageUrl，a.stock，a.fileName，a.noShipCharge，

c.topcategory

FROM prod使用a，categories_products b，类别c

WHERE active = -1 AND homePage = -1

AND a.idProduct = b.idProduct

AND c.idcategory = b.idcategory

AND prodType = 1 ORDER BY a.idProduct DESC


这将按预期返回所有产品，以及任何产品产品在多个TopCategory中是
。


任何想法如何克服这一点将不胜感激。


干杯


Craig

Hi everyone

Have a problem I would areally appreciate help with.
I have 3 tables in a standard format for a Bookshop, eg

Products
Categories
Categories_Products

the latter allowing me to have products in multiple categories.

Everthing works well except for one annoying little thing.

When an individual product (which is in more than one topcategory) is added
to the Shopping Cart it displays twice, because in my select statement I
have the Category listed. I realise I could remove the TopCategory from the
statement and that makes my DISTINCT work as I wanted, but Id prefer to have
the TopCategory as it saves me later having to another SQL query (Im already
doing one to allow me not to list category in the Statement .... but If I
can overcome this one ... then I can remove this as well).

Here is my table structure (the necessary bits)
products
idProduct int
....

categories
idcategory int
idParentCategory int
topcategory int
...

categories_products
idCatProd int
idProduct int
idCategory

When I run a query such as

SELECT DISTINCT a.idProduct, a.description,a.descriptionLong,
a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge,
c.topcategory
FROM products a, categories_products b, categories c
WHERE active = -1 AND homePage = -1
AND a.idProduct = b.idProduct
AND c.idcategory=b.idcategory
AND prodType = 1 ORDER BY a.idProduct DESC

This will return all products as expected, as well as any products which are
in more than one TopCategory.

Any ideas how to overcome this would be greatly appreciated.

Cheers

Craig

推荐答案

2004年5月14日星期五09:05:08 +1200 ，Craig Hoskin写道：

On Fri, 14 May 2004 09:05:08 +1200, Craig Hoskin wrote:

大家好

有问题我会非常感谢帮助。
我有3个标准格式的表格书店，例如

产品类别
分类_产品

后者允许我有多个类别的产品。

除了一件令人讨厌的小东西外，Everthing效果很好当一个单独的产品（在一个以上的topcategory中）被添加到购物车时它会显示两次，因为在我的选择声明中我列出了类别。我意识到我可以从
语句中删除TopCategory并使我的DISTINCT按照我的意愿工作，但我更喜欢使用TopCategory，因为它后来又省去了另一个SQL查询（我已经
这里是我的表结构（必要的位）
产品
idProduct int
....

类别idcategory int
idParentCategory int
topcategory int
.............................................................................................................查询如

SELECT DISTINCT a.idProduct，a.description，a.descriptionLong，
a.listPrice，a.price，a.smallImageUrl，a.stock，a.fileName，a .noShipCharge，
c.topcategory
FROM产品a，categories_products b，类别c
WHERE active = -1 AND homePage = -1
AND ai dProduct = b.idProduct
AND c.idcategory = b.idcategory
AND prodType = 1 ORDER BY a.idProduct DESC

这将按预期返回所有产品，以及任何产品都在多个TopCategory中。

任何想法如何克服这一点将不胜感激。

干杯

克雷格

Hi everyone

Have a problem I would areally appreciate help with.
I have 3 tables in a standard format for a Bookshop, eg

Products
Categories
Categories_Products

the latter allowing me to have products in multiple categories.

Everthing works well except for one annoying little thing.

When an individual product (which is in more than one topcategory) is added
to the Shopping Cart it displays twice, because in my select statement I
have the Category listed. I realise I could remove the TopCategory from the
statement and that makes my DISTINCT work as I wanted, but Id prefer to have
the TopCategory as it saves me later having to another SQL query (Im already
doing one to allow me not to list category in the Statement .... but If I
can overcome this one ... then I can remove this as well).

Here is my table structure (the necessary bits)
products
idProduct int
....

categories
idcategory int
idParentCategory int
topcategory int
...

categories_products
idCatProd int
idProduct int
idCategory

When I run a query such as

SELECT DISTINCT a.idProduct, a.description,a.descriptionLong,
a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge,
c.topcategory
FROM products a, categories_products b, categories c
WHERE active = -1 AND homePage = -1
AND a.idProduct = b.idProduct
AND c.idcategory=b.idcategory
AND prodType = 1 ORDER BY a.idProduct DESC

This will return all products as expected, as well as any products which are
in more than one TopCategory.

Any ideas how to overcome this would be greatly appreciated.

Cheers

Craig

嗨克雷格，


第一个想法必须来自你。 SQL Server不会为您做出

选择。因此，您必须决定*哪个* TopCategory显示为多个TopCategory中的
产品。


如果您可以描述如何选择，我（或者这个NG中的其他人）

可能能够帮助撰写查询。


Best，Hugo

- -


（删除_NO_和_SPAM_以获取我的电子邮件地址）

Hi Craig,

The first idea will have to come from you. SQL Server won''t make your
choices for you. So you must decide *which* TopCategory to display for
products that are in more than one TopCategory.

If you can describe how to choose, I (or someone else in this NG) will
probably be able to help writing up the query.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Doooh


总是这样，你发一个问题并找到一个可能的解决方案:-)


这会有用吗？


选择一个.idProduct，a.description，a.descriptionLong，

a.listPrice，a.price，a.smallImageUrl，a.stock，a.fileName，a.noShipCharge

来自产品a

如果存在（选择*来自类别c，categories_products b其中

a.idProduct = b.idProduct AND c.idcategory = b.idcategory）

AND a.active = -1 AND a.homePage = -1 AND a.prodType = 1

ORDER BY a.idProduct DESC


干杯


Craig

Doooh

Always the way, you post a question and find a possible solution :-)

Would this work?

select a.idProduct, a.description,a.descriptionLong,
a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge
from Products a
where exists (select * from categories c, categories_products b where
a.idProduct = b.idProduct AND c.idcategory=b.idcategory)
AND a.active = -1 AND a.homePage = -1 AND a.prodType = 1
ORDER BY a.idProduct DESC

Cheers

Craig

大家好

有问题我真的很感激帮助。
我有3张桌子书店的标准格式，例如

产品类别
类别_产品

后者允许我拥有多个类别的产品。
<除了一个讨厌的小东西外，它的效果很好。

当一个单独的产品（在一个以上的topcategory中）是
添加到购物车时，它会显示两次，因为在我的选择声明我已经列出了类别。我意识到我可以从
语句中删除TopCategory，这使得我的DISTINCT按照我的意愿工作，但我更喜欢
有TopCategory，因为它稍后会保存我的另一个SQL查询（Im
已经做了一个允许我不在列表中列出类别......但是如果我能够克服这个...那么我也可以删除它。

这里是我的表结构（必要的位）
产品
idProduct int
....

类别idcategory int
idParentCategory int
topcategory int
.............................................................................................................查询如

SELECT DISTINCT a.idProduct，a.description，a.descriptionLong，
a.listPrice，a.price，a.smallImageUrl，a.stock，a.fileName，a .noShipCharge，
c.topcategory
FROM products a，categories_products b，categories c
WHERE active = -1 AND homePage = -1
产品= b.idProduct
AND c.idcategory = b.idcategory
AND prodType = 1 ORDER BY a.idProduct DESC

这将按预期退回所有产品，以及
多于一个TopCategory的任何产品。

任何想法如何克服这一点将不胜感激。

干杯

克雷格

Hi everyone

Have a problem I would areally appreciate help with.
I have 3 tables in a standard format for a Bookshop, eg

Products
Categories
Categories_Products

the latter allowing me to have products in multiple categories.

Everthing works well except for one annoying little thing.

When an individual product (which is in more than one topcategory) is added to the Shopping Cart it displays twice, because in my select statement I
have the Category listed. I realise I could remove the TopCategory from the statement and that makes my DISTINCT work as I wanted, but Id prefer to have the TopCategory as it saves me later having to another SQL query (Im already doing one to allow me not to list category in the Statement .... but If I
can overcome this one ... then I can remove this as well).

Here is my table structure (the necessary bits)
products
idProduct int
....

categories
idcategory int
idParentCategory int
topcategory int
...

categories_products
idCatProd int
idProduct int
idCategory

When I run a query such as

SELECT DISTINCT a.idProduct, a.description,a.descriptionLong,
a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge,
c.topcategory
FROM products a, categories_products b, categories c
WHERE active = -1 AND homePage = -1
AND a.idProduct = b.idProduct
AND c.idcategory=b.idcategory
AND prodType = 1 ORDER BY a.idProduct DESC

This will return all products as expected, as well as any products which are in more than one TopCategory.

Any ideas how to overcome this would be greatly appreciated.

Cheers

Craig

" Hugo Kornelis" < hugo@pe_NO_rFact.in_SPAM_fo>在消息中写道

news：7u ******************************** @ 4ax.com ...

你好Hugo

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:7u********************************@4ax.com...
Hi Hugo

第一个想法必须来自你。 SQL Server不会为您做出选择。因此，您必须决定*哪个* TopCategory要显示多个TopCategory中的产品。


对不起，是的，我应该对这方面进行过苛刻。答：我不在乎:-)

任何topcategory都可以作为它仍然是相同的产品，而product

显示页面实际上会列出它所在的所有类别。 br />

感谢您抽出宝贵时间帮助我。


干杯


Craig

如果你可以描述如何选择，我（或这个NG中的其他人）可能会帮助编写查询。

Best，Hugo -

（删除_NO_和_SPAM_以获取我的电子邮件地址）

The first idea will have to come from you. SQL Server won''t make your
choices for you. So you must decide *which* TopCategory to display for
products that are in more than one TopCategory.
Sorry, yes I should have calrified that aspect. Answer: I dont care :-)
Any topcategory will do as its still the same product, and the produut
display page will actually list all the categories its in.

Thanks for taking the time to help me.

Cheers

Craig
If you can describe how to choose, I (or someone else in this NG) will
probably be able to help writing up the query.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

这篇关于选择带有JOIN的DISTINCT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！