Mega Code Archive

Join columns with full table name reference

SQL> CREATE TABLE emps ( 2 emp varchar(30) 3 ,title varchar(30) 4 ); Table created. SQL> SQL> INSERT INTO emps VALUES ('Tom','Programmer'); 1 row created. SQL> INSERT INTO emps VALUES ('Jack','Tester'); 1 row created. SQL> INSERT INTO emps VALUES ('Mary','Technician'); 1 row created. SQL> SQL> CREATE TABLE JobLevel ( 2 title varchar(30) 3 ,rank varchar(30) 4 ); Table created. SQL> SQL> INSERT INTO JobLevel VALUES ('Programmer','Level1'); 1 row created. SQL> INSERT INTO JobLevel VALUES ('Tester','Level2'); 1 row created. SQL> INSERT INTO JobLevel VALUES ('Technician','Level3'); 1 row created. SQL> SQL> CREATE TABLE salary ( 2 rank varchar(30) 3 ,payment DECIMAL(10,2) 4 ); Table created. SQL> SQL> INSERT INTO salary VALUES ('Level1',2000.00); 1 row created. SQL> INSERT INTO salary VALUES ('Level2',3000.00); 1 row created. SQL> INSERT INTO salary VALUES ('Level3',5000.00); 1 row created. SQL> INSERT INTO salary VALUES ('Level4',6000.00); 1 row created. SQL> SQL> select * from emps; EMP ------------------------------ TITLE ------------------------------ Tom Programmer Jack Tester Mary Technician 3 rows selected. SQL> select * from JobLevel; TITLE ------------------------------ RANK ------------------------------ Programmer Level1 Tester Level2 Technician Level3 3 rows selected. SQL> select * from salary; RANK PAYMENT ------------------------------ ---------- Level1 2000 Level2 3000 Level3 5000 Level4 6000 4 rows selected. SQL> SQL> SELECT payment FROM salary,JobLevel,emps 2 WHERE salary.rank = JobLevel.rank 3 AND JobLevel.title = emps.title 4 AND emps.emp = 'Jack'; PAYMENT ---------- 3000 1 row selected. SQL> SQL> DROP TABLE emps; Table dropped. SQL> DROP TABLE JobLevel; Table dropped. SQL> DROP TABLE salary; Table dropped. SQL> SQL> SQL>