April 16, 2009 at 8:52 am
I'm not a database guy so my database structure could very well be my problem.
My database consists of three tables: objects, components and assignedcomponents.
objects has a information on the objects, components the same type of thing and assignedcomponents has three fields: id, object and component.
So what I'm trying to do is select an object that has a specific set of components assigned to it.
I can get the result I require with a bunch of AND statements but that isn't practical. I tried using the IN statement but it grabs an object if any of the components in the list are assigned to that object.
Any reading material or solutions / suggestions appreciated.
April 16, 2009 at 9:04 am
The database design sounds okay for that kind of thing. If the components and objects tables are similar enough, it might be better to merge them, but it's not critical, just possible.
The way I'd query that would be to use AND IN. For example:
select *
from dbo.Objects
where ObjectID in
(select ObjectID
from dbo.Objects_Components
where ComponentID = 1)
and ObjectID in
(select ObjectID
from dbo.Objects_Components
where ComponentID = 2);
That would get you all the objects that have component 1 AND component 2. You could add some Not In statements if you want to eliminate other components.
If the join table (which I called Objects_Components in my example above) has one entry per combination, enforced by a constraint/index/key, then you could easily add a piece to the above that would limit it to objects that had those two components and NO others, by adding:
and ObjectID in
(select ObjectID
from dbo.Objects_Components
group by ObjectID
having count(*) = 2)
You'd add that to the Where clause, of course.
Does that help?
Where this kind of thing can get tricky is if you are going to be feeding it lists of component IDs that are variable. Can be done, but it's a bit trickier. Is that the situation you're looking at?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 16, 2009 at 10:28 am
I am trying to feed a variable list of component ids.
April 17, 2009 at 7:10 am
Anymore idea's from anyone?
April 17, 2009 at 7:43 am
It'll need to look something like this:
-- Build and populate test tables
create table #Objects (
ID int identity primary key);
insert into #Objects
default values;
insert into #Objects
default values;
create table #Components (
ID int identity primary key);
insert into #Components
default values;
insert into #Components
default values;
insert into #Components
default values;
insert into #Components
default values;
insert into #Components
default values;
create table #Objects_Components (
ObjectID int not null,
ComponentID int not null,
constraint PK_ObjsComps primary key (ObjectID, ComponentID));
insert into #Objects_Components (ObjectID, ComponentID)
select 1, 1 union all
select 1, 3 union all
select 2, 2 union all
select 2, 4 union all
select 2, 5;
-- This will actually be an input parameter, not a variable, in the final solution
declare @ComponentIDs_in varchar(100);
select @ComponentIDs_in = '1,3';
-- The Query
;with
CompIDs (CompID) as -- String parser CTE
(select cast(substring(@ComponentIDs_in + ',', Number, charindex(',', @ComponentIDs_in + ',', Number) - Number) as Int)
from dbo.Numbers -- Requires a Numbers/Tally table here
where Number <= len(@ComponentIDs_in)
and substring(',' + @ComponentIDs_in, Number, 1) = ','),
ObjIDs (ObjectID) as -- Picks the object IDs that match the input parameter
(select ObjectID
from #Objects_Components ObjComps
inner join CompIDs
on ObjComps.ComponentID = CompID
group by ObjectID
having count(*) = (select count(*) from CompIDs)) -- Makes sure it has all the components selected and no others
select #Objects.*
from #Objects
inner join ObjIDs
on ID = ObjectID;
This will only work if you have a Numbers table. If you don't, let me know and I'll show you how to create one (they're really useful, and not just for this).
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2009 at 8:10 am
Hi Richard,
I will not describe how to parse a list of values into a table - that has been topic of many posts and you can find it easily. If you want to know more, look up articles by Jeff Moden here at SQLS Central and Erland Sommarskog's Arrays and Lists in SQL Server 2005.
So, let's suppose you already have the list in a table variable called @search.
/*Create test data*/
CREATE TABLE #objects(id_object INT, descr varchar(20))
CREATE TABLE #components(id_component INT, descr varchar(20))
CREATE TABLE #assignedcomp(id_object INT, id_component INT)
INSERT INTO #objects(id_object, descr)
SELECT 1, 'object 1'
UNION SELECT 2, 'object 2'
UNION SELECT 3, 'object 3'
UNION SELECT 4, 'object 4'
INSERT INTO #components(id_component, descr)
SELECT 1, 'Comp 1'
UNION SELECT 2, 'Comp 2'
UNION SELECT 3, 'Comp 3'
UNION SELECT 4, 'Comp 4'
UNION SELECT 5, 'Comp 5'
UNION SELECT 6, 'Comp 6'
UNION SELECT 7, 'Comp 7'
UNION SELECT 8, 'Comp 8'
INSERT INTO #assignedcomp(id_object, id_component)
SELECT 1, 1
UNION SELECT 1, 2
UNION SELECT 1, 6
UNION SELECT 2, 2
UNION SELECT 2, 4
UNION SELECT 2, 5
UNION SELECT 2, 8
UNION SELECT 3, 2
UNION SELECT 4, 2
UNION SELECT 4, 3
UNION SELECT 4, 4
UNION SELECT 4, 5
UNION SELECT 4, 6
/*Pass list of component ID's*/
DECLARE @search TABLE (id INT)
INSERT INTO @search(id)
SELECT 2
UNION SELECT 4
/*
Find objects using this set of components
(include also objects that contain other components on top of required components)
*/
SELECT o.descr as Objects_found
FROM #assignedcomp a
JOIN @search s ON s.id = a.id_component
JOIN #objects o ON o.id_object = a.id_object
GROUP BY o.descr
HAVING count(*) = (SELECT count(*) from @search)
/*Cleanup*/
DROP TABLE #objects
DROP TABLE #components
DROP TABLE #assignedcomp
If you need some more explanation or if it doesn't work correctly with certain combination of values, let us know. I didn't test this solution extensively, but I know that I already did something similar before and it worked.
PS. I started to write before GSquared posted, but since I'm slower, he posted first without me knowing about it - so that's why there are 2 very similar solutions one after another. Well done, GSquared, you even managed to include the parsing part that I decided to skip.
April 22, 2009 at 9:41 am
Thank you both for the help, I was able to achieve exactly what I was looking for.:-)
April 22, 2009 at 9:49 am
You're welcome. Glad we could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply