query many to many attributes table

Question

Post reply

query many to many attributes table

4Advanced

Newbie

Points: 3
More actions
January 15, 2010 at 9:22 am

#137497

Hi there,
I can't figure out how to get the correct resultset back from a query (stored procedure).
There are 2 tables involved, Machine and MachineAttributeValue.
The MachineAttributeValue stores the individual attributes (ID's) for a specific machine (ID) which means it has 2 columns (MachineID and AttributeValueID)
Assume the MachineAttributeValue has the following records
MachineID | AttributeValueID
1 | 1
1 | 33
1 | 66
1 | 67
1 | 68
2 | 1
3 | 1
All i want to accomplish is getting the results back for each machine which has the AttributeValueID's 1 AND 33
Can any of you give me a clue to get me on the right track?
Regards,
Richard
The Netherlands

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Untested but should be fine.

with cteRequiredValues

as

(

select 1 as value

union all

select 33

)

Select MachineID

from MachineAttributeValue

join cteRequiredValues

on cteRequiredValues.Value = AttributeValueID

group by MachineID

having count(*) =2

Clear Sky SQL
My Blog[/url]

4Advanced Newbie Points: 3 More actions · Answer 2

Dave,

Thanks a lot for your fast answer :=)

It gives me the correct resultset.

The point is, and maybe I should have mentioned before, that the 1 & 33 are just a guess. The table MachineAttributeValue will hold tons of records and a machine (or machines) can be selected on 9 different options (Attributes).

So the query needs to be dynamic.....

Thx

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

So .. something like this ?

Create table Machine

(

MachineId integer,

AttributeId integer)

go

Create table MachineAttributes

(

MachineId integer,

AttributeId integer)

go

insert into Machine values(1,1)

insert into Machine values(1,2)

insert into Machine values(1,3)

insert into Machine values(1,4)

insert into Machine values(1,5)

insert into Machine values(2,1)

insert into Machine values(2,2)

insert into Machine values(2,3)

insert into Machine values(2,4)

insert into Machine values(2,5)

go

insert into MachineAttributes values(1,1)

insert into MachineAttributes values(1,2)

insert into MachineAttributes values(1,3)

insert into MachineAttributes values(1,4)

insert into MachineAttributes values(1,5)

insert into MachineAttributes values(2,1)

insert into MachineAttributes values(2,2)

insert into MachineAttributes values(2,3)

insert into MachineAttributes values(2,4)

go

select Machine.MachineId from Machine left join MachineAttributes

on Machine.MachineId = MachineAttributes.MachineId

and Machine.AttributeId= MachineAttributes.AttributeId

group by Machine.MachineId

having COUNT(Machine.AttributeId) = COUNT(machineAttributes.MachineId)

Clear Sky SQL
My Blog[/url]

4Advanced Newbie Points: 3 More actions · Answer 4

Dave,

Thanks for your reply :=)

Yesterday I've send you a PM with my stored procedure which functions well.... Maybe you have some thoughts about that?

It should be one big dynamic sp because the parameters will vary by each selection.

Regards,

Richard

The Netherlands

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

For the record its best to keep all conversations in the public forum, other people may ( and probably will ) spot issues i miss.

You are using a ## (global temp table) change that to # (session temp table)

Take a look here http://www.sommarskog.se/dynamic_sql.html for issues with dynamic sql

Clear Sky SQL
My Blog[/url]