Re: SQL help needed...

From: <fitzjarrell_at_cox.net>
Date: 14 Apr 2005 06:11:23 -0700
Message-ID: <1113484283.126805.213740@o13g2000cwo.googlegroups.com>

Comments embedded.

Raj wrote:
> Hi,
>
>
> I have a table in which two fields(FirstID, SecondID) together make
the
>
> primary key for the table. Let's look at the following example:
>
>
> FirstID SecondID
> ******* ********
> 2 2
> 2 3
> 2 5
> 5 5
> 5 7
> 8 7
> 9 10
> 11 12
>
>
> Here is what I am trying to do. For a given FirstID (say, 2) find all
> SecondID with this FirstID (2,3,5). Now for these second ID's is
there
> a FirstID other than 2? (Yes, 5). I would say FirstID=2 is related to
> FirstID = 5. Extending the logic FirstID = 5 is related to FirstID =
8.
>

I'm not clear on how you're defining 'related', other than the FirstID values in some way share a SecondID value. Of course, you're getting into 'remote relations' with your first 'example' as Firstid=8 had no direct 'relation' to FirstID=2. Possibly you're attempting to find all FirstID values in a basic tree structure, where FirstIDs 2, 5, and 8 satisfy such a condition. You really need to be much more clear in your description.

> I am trying to write a SQL (some kind of self join, I think) which
will
> give me all related FirstID for a given First ID. For First ID = 2
the
> SQL will give me 5 and 8 in the above example. For First ID = 9 the
SQL
> will give me 11.

Not with the data you've posted, as FirstID=9 has absolutely NOTHING in common with FirstID=11. Possibly this was a typographical error, and you really meant for FirstID=11 to have SecondID=10; in that case your desired results could be produced.

> Any help will be greatly appreciated. Thanks!!
>

Okay, let's try your data and see where we end up:

SQL> create table stupidtest(firstid number, secondid number);

Table created.

SQL> insert into stupidtest values (2,2);

insert into stupidtest values (2,3);
insert into stupidtest values (2,5);
insert into stupidtest values (5,5);
insert into stupidtest values (5,7);
insert into stupidtest values (8,7);
insert into stupidtest values (9,10);
insert into stupidtest values (11,12);

1 row created.

SQL>
1 row created.

SQL> commit
2 /

Commit complete.

SQL> I've modified the query already supplied you to use the table just created. Let's see what is returned:

SQL> l
  1 SELECT *
  2 FROM stupidtest
  3 WHERE SecondID in (SELECT SecondID FROM stupidtest WHERE FirstID=&&1)
  4* AND FirstID <> &&1
SQL> /
Enter value for 1: 2
old 3: WHERE SecondID in (SELECT SecondID FROM stupidtest WHERE FirstID=&&1)
new 3: WHERE SecondID in (SELECT SecondID FROM stupidtest WHERE FirstID=2)
old 4: AND FirstID <> &&1
new 4: AND FirstID <> 2

FIRSTID SECONDID
---------- ----------

5 5

SQL> So far, so good. Let's now try FirstID = 5:

SQL> l
  1 SELECT *
  2 FROM stupidtest
  3 WHERE SecondID in (SELECT SecondID FROM stupidtest WHERE FirstID=&&1)
  4* AND FirstID <> &&1
SQL> /
old 3: WHERE SecondID in (SELECT SecondID FROM stupidtest WHERE FirstID=&&1)
new 3: WHERE SecondID in (SELECT SecondID FROM stupidtest WHERE FirstID=5)
old 4: AND FirstID <> &&1
new 4: AND FirstID <> 5

FIRSTID SECONDID
---------- ----------

         2          5
         8          7

SQL> Again, results you've expected. However, note tha supplying 2 as the FirstID value did NOT return any values outside of 5, not exactly as you wanted. Now let's try firstID = 9:

SQL> l
  1 SELECT *
  2 FROM stupidtest
  3 WHERE SecondID in (SELECT SecondID FROM stupidtest WHERE FirstID=&&1)
  4* AND FirstID <> &&1
SQL> /
Enter value for 1: 9
old 3: WHERE SecondID in (SELECT SecondID FROM stupidtest WHERE FirstID=&&1)
new 3: WHERE SecondID in (SELECT SecondID FROM stupidtest WHERE FirstID=9)
old 4: AND FirstID <> &&1
new 4: AND FirstID <> 9

no rows selected

SQL> As it should be, since there exists no 'relation' between FirstID=9 and FirstID=11. Let's change the data a bit and see if this fixes the 'problem':

SQL> update stupidtest set secondid = 10 where secondid =12;

1 row updated.

SQL> commit;

Commit complete.

SQL> l
  1 SELECT *
  2 FROM stupidtest
  3 WHERE SecondID in (SELECT SecondID FROM stupidtest WHERE FirstID=&&1)
  4* AND FirstID <> &&1
SQL> /
old 3: WHERE SecondID in (SELECT SecondID FROM stupidtest WHERE FirstID=&&1)
new 3: WHERE SecondID in (SELECT SecondID FROM stupidtest WHERE FirstID=9)
old 4: AND FirstID <> &&1
new 4: AND FirstID <> 9

FIRSTID SECONDID
---------- ----------

11 10

Now the 'relationship' you've envisioned exists, however, as stated before, your definition of 'relateionship' needs to be better described.

Work on your 'problem definition' a bit and come back when you can more reliably define and describe it.

David FItzjarrell

>
> -Raj
Received on Thu Apr 14 2005 - 08:11:23 CDT