Help with relational query - many to many

Hi all! I am working on a piece of SQL at the moment and I'm getting a little confused.
I have 3 tables: Items, Attributes and a table linking them. I have 5 attributes and an item can have any of the 5 attributes. So my linking table holds the ItemID and the AttributeID and there can be 1-5 entries for each Item.
A user can search for items based on Attributes; so they can tick 5 checkboxes that represent the 5 Attributes. So I need to build a query based on their choices. At the moment I'm using:
Select * FROM Items
INNER JOIN linking on Link_ItemID = Item_ID
WHERE Link_AttributeID IN (10, 13, 17)
But this brings out the Item that have either AttributeID of 10 or 13 or 17 whereas I need it to pull outONLYitems that have a AttributeID of 10 AND 13 AND 17.
Can anyone help with this query? Sorry if this is badly worded. The solutions is prolly something really simple I have overlooked... :S
I've also tried:
Select * FROM Items
INNER JOIN linking on Link_ItemID = Item_ID
WHERE Link_AttributeID = 10 AND AttributeID = 13 etc
But obviously that won't work! :sSelect *
from items i
where exists (select null from linking where link_itemID = i.item_id and link_attributeID = 10) and
exists (select null from linking where link_itemID = i.item_id and link_attributeID = 13) and
exists (select null from linking where link_itemID = i.item_id and link_attributeID = 17)

Nick|||Sorry for the late reply! Thanks for your post nick!
i got it working :)

Help with multiple counts..

I have a application table that I have turned into a cube. It includes attributes of a organizaiton name, month, and year. This all works fine.

I would like to add another table to the cube that counts a visits. It also has attributes of organization name, month and year.

I am confused where I make the associations so they can operate in the same cube.

Do I join the tables in the datasource and just add the measure in the cube? Do I add a new dimention in the cube?

Any help in this area would be great. I am basically looking for a cube like so..

Month Year

Organization Name ApplicationCount (From the application table)

Visit Count (From the visit table)

Thanks in advance,

Mardo

If it has a similar structure, just a different measure, then you could add this table as a separate measure group with a measure in it that maps to the Visit Count. If these two table are completely identical you might be able to join them with a view or a named query in the DSV so that Visit Count is just added as an extra column to the existing cube, this might be a more efficient approach.

Help with MDX selecting multiple attributes in Where clause

Given the following:
Dimension Name: Issues
Measure: Issue Count
Dimension Attributes: Priority (Values 1,2,3,4)
Severity (Values 1,2,3,4)
How can I format an MDX statement to select Issues Where (Priority = 1 or
Priority = 2) and (Severity = 1 or Severity = 2)?
When I try the following:
select [Measures].[Issue Count] on columns,
([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/2005]) on rows
From [Issues DB]
where
{
([Issues].[Priority].&[1]),([Issues].[Priority].&[2]),
([Issues].[Severity].&[1]), [Issues].[Severity].&[2]
}
I get the error referenced below:
"Members belong to different hierarchies in the function."
Any pointers would be greatly appreciated!
~Steven
I guess you treat Priority and Severity as same level and put them between
{}, which is for a set.
Try this:
(
{[Issues].[Priority].&[1], [Issues].[Priority].&[2]},
{[Issues].[Severity].&[1], [Issues].[Severity].&[2]}
)
In your case, I would say put these two properties in 2 hierarchies. which
may improve the performance.
Guangming
"Steven" wrote:

> Given the following:
>
> Dimension Name: Issues
> Measure: Issue Count
> Dimension Attributes: Priority (Values 1,2,3,4)
> Severity (Values 1,2,3,4)
>
> How can I format an MDX statement to select Issues Where (Priority = 1 or
> Priority = 2) and (Severity = 1 or Severity = 2)?
>
> When I try the following:
>
> select [Measures].[Issue Count] on columns,
> ([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/2005]) on rows
> From [Issues DB]
> where
> {
> ([Issues].[Priority].&[1]),([Issues].[Priority].&[2]),
> ([Issues].[Severity].&[1]), [Issues].[Severity].&[2]
> }
>
> I get the error referenced below:
>
> "Members belong to different hierarchies in the function."
>
> Any pointers would be greatly appreciated!
>
> ~Steven
>
>

Help with MDX selecting multiple attributes in Where clause

Given the following:
Dimension Name: Issues
Measure: Issue Count
Dimension Attributes: Priority (Values 1,2,3,4)
Severity (Values 1,2,3,4)
How can I format an MDX statement to select Issues Where (Priority = 1 or
Priority = 2) and (Severity = 1 or Severity = 2)?
When I try the following:
select [Measures].[Issue Count] on columns,
([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/20
05]) on rows
From [Issues DB]
where
{
([Issues].[Priority].&[1]),([Issues].[Priority].&[2]
),
([Issues].[Severity].&[1]), [Issues].[Severity].&[2]
}
I get the error referenced below:
"Members belong to different hierarchies in the function."
Any pointers would be greatly appreciated!
~StevenI guess you treat Priority and Severity as same level and put them between
{}, which is for a set.
Try this:
(
{[Issues].[Priority].&[1], [Issues].[Priority].&
1;2]},
{[Issues].[Severity].&[1], [Issues].[Severity].&
1;2]}
)
In your case, I would say put these two properties in 2 hierarchies. which
may improve the performance.
Guangming
"Steven" wrote:

> Given the following:
>
> Dimension Name: Issues
> Measure: Issue Count
> Dimension Attributes: Priority (Values 1,2,3,4)
> Severity (Values 1,2,3,4)
>
> How can I format an MDX statement to select Issues Where (Priority = 1 or
> Priority = 2) and (Severity = 1 or Severity = 2)?
>
> When I try the following:
>
> select [Measures].[Issue Count] on columns,
> ([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/
2005]) on rows
> From [Issues DB]
> where
> {
> ([Issues].[Priority].&[1]),([Issues].[Priority].&[
2]),
> ([Issues].[Severity].&[1]), [Issues].[Severity].&[
2]
> }
>
> I get the error referenced below:
>
> "Members belong to different hierarchies in the function."
>
> Any pointers would be greatly appreciated!
>
> ~Steven
>
>