| Create Sql Query [message #615524] |
Thu, 05 June 2014 03:33  |
Xandot
Messages: 235
Registered: January 2014
Location: India
|
Senior Member |
|
|
Hello All,
I want to create one query with the help of three tables like ..
create table tab1 (Pid number ,value number);
create table tab2 (Eid number ,Pid number);
create table tab3 (Did number ,Eid number,l_data varchar2(50));
alter table "TAB2" add constraint FK1 foreign key("PID") references "TAB1"("PID");
alter table "TAB3" add constraint FK2 foreign key("EID") references "TAB2"("EID");
insert into tab1(pid,value) values (101,1);
insert into tab2(Eid,PID) values (201,101);
insert into tab3(DID,EID,L_DATA) values (501,201,'Active');
insert into tab3(DID,EID,L_DATA) values (502,201,'Inactive');
When i am going to select the data from tab1 then the "value" should be pull out from tab3 and "value" 1 stands for 'Active' (501)
and 2 stands for 'Inactive'(502).
so the final output like ..
PID | VALUE | EID
---------------------
101 | Active | 201
Thanks,
Xandot
|
|
|
|
|
|
|
|
| Re: Create Sql Query [message #615530 is a reply to message #615529] |
Thu, 05 June 2014 03:54  |
Xandot
Messages: 235
Registered: January 2014
Location: India
|
Senior Member |
|
|
Its simple but if the tab3 contains 100 rows like 501..600 then the user can select value between 0..99 so how can i manage the in the query because user can select any number in the value field between 0 to 99 ..
-----------MY Query ----------
select distinct A.PID,decode(a.value,1,'Active',0,'Inactive'),C.EID from tab1 a,tab2 b,tab3 c
where a.pid=b.pid
and b.eid=c.eid
----------------------
But how many times i can put decode condition here ...can i use row_number for 100 rows filtration..
Thanks
[Updated on: Thu, 05 June 2014 04:05] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Create Sql Query [message #615536 is a reply to message #615530] |
Thu, 05 June 2014 04:14 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
SQL> -- Create tables
SQL> CREATE TABLE tab1
2 (
3 pid NUMBER PRIMARY KEY,
4 value NUMBER
5 );
Table created
SQL> CREATE TABLE tab2
2 (
3 eid NUMBER PRIMARY KEY,
4 pid NUMBER
5 );
Table created
SQL> CREATE TABLE tab3
2 (
3 did NUMBER,
4 eid NUMBER,
5 l_data VARCHAR2(50)
6 );
Table created
SQL> --Insert values
SQL> INSERT INTO tab1
2 (pid,
3 value)
4 VALUES (101,
5 1);
1 row inserted
SQL> INSERT INTO tab2
2 (eid,
3 pid)
4 VALUES (201,
5 101);
1 row inserted
SQL> INSERT INTO tab3
2 (did,
3 eid,
4 l_data)
5 VALUES (501,
6 201,
7 'Active');
1 row inserted
SQL> INSERT INTO tab3
2 (did,
3 eid,
4 l_data)
5 VALUES (502,
6 201,
7 'Inactive');
1 row inserted
SQL> -- query
SQL> SELECT t1.pid, t3.l_data, t2.eid FROM
2 tab1 t1, tab2 t2, tab3 t3
3 WHERE t1.pid=t2.pid
4 AND t2.eid=t3.eid
5 AND t3.l_data='Active';
PID L_DATA EID
---------- -------------------------------------------------- ----------
101 Active 201
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
