Re: SQL query problem - can anyone help??

I found a solution but it is UGLY. Anybody trying to understand it later will most likely be lost. I wrote and I'm not sure I understand it, but it

works. You should really use PL/SQL for this type of query.

Here goes (this is assuming you already know which part_seq you want to use):

    select distinct c.part_seq
    from part c, part d
    where c.part_name || d.part_name =

        (select a.part_name|| b.part_name
         from part a , part b
        where  a.part_seq = 72625
           and  a.part_name != b.part_name
           and  rownum = 1)

Let me know if this works for you.

Marc Lodico
mlodico_at_bigfoot.com

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'
>
> In other words, if I query the table for the group of parts that
> has a part_sequence of '72625' I get:
> part_name
> cable_set
> clamp
>
> 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 cannot make changes to the table!
>
> If this is a simple question, I apologize in advance for wasting
> time, I have simply been unable to figure this one out myself...
>
> Thanks in advance!!
>
> -Chris
Received on Thu Jan 15 1998 - 00:00:00 CET