Check if records matching parent have same role

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Check if records matching parent have same role

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2012-04-03 : 10:34:01

Hi,

Sure there must be a straightforward solution to this, but not seeing it currently. Could use some help.

Here's the situation. I have an AttributeMap table that stores the relationships of things to other things. It looks like this:


      [intParentAttributeId]
      ,[intChildAttributeId]
      ,[intRoleId]

This stores all sorts of different relationships for all sorts of different items. In this instance I'm interested in the relationship of a TV series with its constituent episodes: in my table these are stored in the form of the series itemId from a central catalogue as a the intParentAttributeId, the episode itemId from that same central catalogue as intChildAttributeId and the intRoleId matches an Id from an ItemRole table.

For example this SQL will select the itemId's of all the series which have episodes:


select distinct intParentAttributeId from [AttributeMap]
where intRoleId in
	(
	 select intRoleId from
	 [ItemRole]
	 where strEnd1RoleName = 'series' or strEnd1RoleName = 'collection'
	)

So far, so good. Now what I need to do is select the itemId's of those series which not only have episodes (as in the above query) but for which all the episodes within that series have the same 'brand'. Brand is another ItemRole - in this instance its intRoleId happens to be fixed at a value of 9 rather than being variable as the intRoleId of series and collection can be.

It's this second bit that's causing me trouble, the need to collate information about all the episodes in each series and make sure they all have the same brand Id.

Help very much appreciated.

Cheers,
Matt

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-04-03 : 16:32:14

Are "series" and "collection" two examples of "Brand"?[CODE]declare @AttributeMap table (
intParentAttributeId int,
intChildAttributeId int,
intRoleId int
)

declare @Roles table (
RoleId int,
RoleName varchar(20)
)

insert into @Roles
values (1, 'series'),
(2, 'collection'),
(3, 'colors'),
(4, 'blah, blah, blah')

insert into @AttributeMap
values (10, 10, 1), -- Should be found
(10, 11, 1),
(10, 12, 1),

(20, 20, 1), -- Both Roles - Should NOT be found
(20, 21, 2),

(30, 30, 3) -- Single role BUT not in criteria

------------------------

select a.intParentAttributeId
from @AttributeMap a
inner join
@Roles r
on r.RoleId = a.intRoleId
where
r.RoleName in ('series', 'collection')
group by
a.intParentAttributeId
having
count(distinct a.intRoleId) = 1[/CODE]

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources