advanced query problem - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

advanced query problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-28 : 08:22:15

fabrice writes "Hi,

Let be the following tables:

SET : (SET_ID, NAME)
ITEM : (ITEM_ID, NAME)
SET_ITEM : (SET_ID, ITEM_ID, QUANTITY)

For a given SET_ID (let's call it S), I would like to create 2
queries:

- a query that returns all sets (i.e. all SET_ID) that have the same
content as S (i.e., that have exactly the same detail records in
SET_ITEM)

- a query that returns all super-sets of S (i.e., all sets containing
the same detail records in SET_ITEM, or more)

Could you give me some hint to help me write these queries?

Thanks,
Fabrice"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-28 : 20:38:00

hmmm, I'm not real good with advanced query problems, but I can do basic ones....

Think you want something like this....

1.
select a.set_id
from set_item a inner join set_item b
on a.item_id = b.item_id
and a.quantity = b.quantity
where b.set_item = @S

Now when you say "or more" - I'm assuming you mean
with quantity = S.quantity or larger ????
if so
2.
select a.set_id
from set_item a inner join set_item b
on a.item_id = b.item_id
and a.quantity >= b.quantity
where b.set_item = @S

Dunno if that's exactly what you want. perhaps you could provide some example data, and example solution?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-29 : 03:47:48

Joe Celko recently posted some of his stuff on relational division:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=14718

damcalcan
Starting Member

11 Posts

Posted - 2002-05-01 : 00:36:24

- a query that returns all sets (i.e. all SET_ID) that have the same
content as S (i.e., that have exactly the same detail records in
SET_ITEM)

DECLARE @SetId int
SET @SetId = 1 -- the given set

SELECT S.setid
FROM set_item S
WHERE NOT EXISTS (SELECT 1
FROM (SELECT * FROM set_item WHERE setid =@SetId) S1
FULL OUTER JOIN (SELECT * FROM set_item WHERE setid =S.setid) S2
ON S1.itemid= S2.itemid
AND S1.quantity = S2.quantity
WHERE S2.setid IS NULL
OR S1.setid IS NULL)
GROUP BY S.setid

damcalcan
Starting Member

11 Posts

Posted - 2002-05-01 : 00:45:10

- a query that returns all super-sets of S (i.e., all sets containing
the same detail records in SET_ITEM, or more)

DECLARE @SetId int
SET @SetId = 1

SELECT S.setid
FROM set_item S
WHERE NOT EXISTS (SELECT 1
FROM (SELECT * FROM set_item WHERE setid =@SetId) S1
LEFT JOIN (SELECT * FROM set_item WHERE setid =S.setid) S2
ON S1.itemid= S2.itemid
AND S1.quantity = S2.quantity
WHERE S2.setid IS NULL)
GROUP BY S.setid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources