问题描述:

I have some tables setup and linked in a system like so:

Products

  • ActualID PK
  • ProductID
  • ItemID

Items

  • ItemID PK
  • DataID

ItemsDataOne

  • DataID PK
  • Value (value I need)

ItemsDataTwo

  • DataID PK
  • Value (value I need)

I'm having a problem with the following select where there are ProductIDs with more then one instance of themselves:

select names.ProductID, names.Name, descriptions.Desc

from

(select Products.ProductID, ItemsDataOne.Value as Name

from Products

inner join Items on Items.ItemI = Product.ItemID

inner join ItemsDataOne ON ItemsDataOne.DataID = Items.DataID) as names

inner join

(select Products.ProductID, ItemsDataTwo.Value as Desc

from Products

inner join Items on Items.ItemI = Product.ItemID

inner join ItemsDataTwo ON ItemsDataTwo.DataID = Items.DataID) as descriptions

on names.ProductID = descriptions.ProductID

Which returns duplicates like this:

Actual Result

ProductID Name Description

01 "One" "Description One"

01 "Two" "Description One"

01 "One" "Description Two"

01 "Two" "Description Two"

What I want

ProductID Name Description

01 "One" "Description One"

01 "Two" "Description Two"

网友答案:

It can be done more simply without subselects:

select Products.ProductID, ItemsDataOne.Value, ItemsDataTwo.Value
from Products
inner join Items on Items.ItemID = Product.ItemID
inner join ItemsDataOne ON ItemsDataOne.DataID = Items.DataID
inner join ItemsDataTwo ON ItemsDataTwo.DataID = Items.DataID

(You were missing the relation between ItemsDataOne and ItemsDataTwo - they need to have the same DataID.)

相关阅读:
Top