Re: SQL query problem - can anyone help??

Chris Hughes wrote:
>
> I have an interesting SQL query that I cannot seem to make work. I was
> hoping someone more experienced with SQL might be able to lend some
> insight, or maybe let me know if this can even be done given the
> parameters of the problem...
>
> I am querying the following table (data as shown):
>
> part_sequence part_name
> 72625 cable_set
> 72625 clamp
> 72626 cable_set
> 72626 clamp
> 72627 cable_set
>
> What I am attempting to do is obtain the part sequence(s) of the
> group of parts similar to those having part_sequence='72625'
>
> I want to retrieve the part sequence for any other groups that
> also contain the same parts as part_sequence 72625, so in the
> example above, I want to return part_sequence '72626' (since it
> also has parts 'cable_set' and 'clamp') but I do not want to
> return part sequence '72627' (since it only has the part 'cable_set'
> and is missing the part 'clamp').
>
> I have attempted a self-join on the table and this still returns
> both part_sequence '72626' and '72627'.
>
I am not sure what you did.

> I cannot make changes to the table!

You don't have to.

>

Let me simplify the statement of the question itself: We consider the sequence# as the ID of a maker and the part_name as the part that the maker produces.

The query would be:

	find the maker that produces all the parts as maker 72525
 	(72625 not included in your case)

The SQL statement goes like this:
select distinct seq from t t1
where not exists (select name from (select * from t where seq=72625) t3 where not exists (select * from t t2 where t1.seq=t2.seq and t2.name=t3.name)) and t1.seq <> 72625

If you are using relational calculus to consider the problem, it will make you life easier.

-- 

==================
^_^  Wei Tang  ^_^
------------------------------------------------------------------------
* Dept. of Computing Science           | Office:       CAB 499         *
* Univ. of Alberta, Edmonton, Canada   | Phone(O): (403)-492-3976      *
* URL: http://www.cs.ualberta.ca/~wtang| Email: wtang_at_cs.ualberta.ca   * 
------------------------------------------------------------------------