Re: Sqlplus question

Teresa Narvaez <teresa_at_Glue.umd.edu> wrote:
> Hello, I need help in writting a sqlplus code to do the following.
> I have two tables tab1 and tab2 with the following attributes.
>
> tab1 :
> Name Null? Type
> ------------------------------- -------- ----
> LAYOUT NOT NULL VARCHAR2(20)
> SUBNET_ID NOT NULL VARCHAR2(5)
> FAC_ID NOT NULL VARCHAR2(3)
> X_POS NOT NULL NUMBER(3)
> Y_POS NOT NULL NUMBER(3)
>
> tab2:
> Name Null? Type
> ------------------------------- -------- ----
> FAC_ID NOT NULL VARCHAR2(3)
> TRK_GRP_NUM NOT NULL NUMBER(4)
> SW_TRK_GRP_NUM NOT NULL NUMBER(4)
> SCLLI VARCHAR2(9)
> CLLI VARCHAR2(16)
> NM_TRK_GRP_TYPE NOT NULL NUMBER(1)
> DEST_FAC_ID VARCHAR2(3)
> DEST_SW_TRK_GRP_NUM NUMBER(4)
> UPD_DATE DATE
>
>
> I need to get all the fac_id, x_pos, dest_fac_id such
>that subnet_id = 'japan' and fac_id in tab1 = fac_id in tab2.
>Also, the dest_fac_id is itself a fac_id and i want to make
>sure that i get all the dest_fac_id's only for subnet_id='japan'.
>
> In other words, fac_id and dest_fac_id are names of
>nodes which i need to connect (i.e. fac_id ------ dest_fac_id).
>But I do not want to consider connecting fac_id to dest_fac_id if
>dest_fac_id is not in the subnet of japan. I tried acomplishing
>by joining tab1 and tab2, is there a way to do this without
>joining the tables??
>

Teresa, try the following:

SELECT t1.fac_id, t1.x_pos, dest_fac_id
FROM tab1 t1, tab1 t1a, tab2 t2
WHERE t1.subnet_id='japan'

      and t1.fac_id=t2.fac_id
      and t2.dest_fac_id=t1a.fac_id
      and t1a.subnet_id='japan'

I did this quick and may not have all the details down. But the basic strategy is to find the fac_id's where the subnet_id='japan' from tab1, get the dest_fac_id from tab2, and then go back to tab1 to check that the subnet_id for the dest_fac_id is also 'japan'.

Good luck.

Beth Received on Tue Nov 07 1995 - 00:00:00 CET