Marek at 13:21, 12 July 2012

2012-07-12T13:21:42Z

Marek at 13:18, 12 July 2012

2012-07-12T13:18:46Z

Marek: Created page with "How do you use Equi Join to join two tables with the same name?

I am trying to join two tables with the same name. I am required to use an inner join Al..."

2012-07-11T11:47:36Z

Created page with "How do you use Equi Join to join two tables with the same name? <div class='ht'> <div> I am trying to join two tables with the same name. I am required to use an inner join Al..."

New page

How do you use Equi Join to join two tables with the same name?
<div class='ht'>
<div>
I am trying to join two tables with the same name. I am required to use an inner join
Also I need to show the managers name.
Question is:
Join the employee table with the employee table and department table.
Show the employee id, name, dept code, manager id, manager name,
managers department code, and name for that (mgr's) department.

We have a self join, each copy of the table is given an "alias" - here we
use w for the worker and b for the boss. We can treat these as different tables.
You get an inner join by default - this means that Robin (who has no boss)
does not show up in the results.
</div>
<source lang=sql class='tidy'>

</source>
<source lang='sql' class='def e-access'>CREATE TABLE employee(
employee_id INTEGER PRIMARY KEY,
first_name VARCHAR(10),
dept_code VARCHAR(10),
manager_id INTEGER REFERENCES employee);
INSERT INTO employee VALUES (1,'Robin','Eng',NULL);
INSERT INTO employee VALUES (2,'Jon','SoC',1);
INSERT INTO employee VALUES (3,'Andrew','SoC',2);
INSERT INTO employee VALUES (4,'Alison','SoC',2);
SELECT w.first_name as worker, b.first_name as boss
FROM employee w, employee b
WHERE w.manager_id = b.employee_id</source>
<source lang='sql' class='def e-oracle'>CREATE TABLE employee(
employee_id INTEGER PRIMARY KEY,
first_name VARCHAR(10),
dept_code VARCHAR(10),
manager_id INTEGER REFERENCES employee);
INSERT INTO employee VALUES (1,'Robin','Eng',NULL);
INSERT INTO employee VALUES (2,'Jon','SoC',1);
INSERT INTO employee VALUES (3,'Andrew','SoC',2);
INSERT INTO employee VALUES (4,'Alison','SoC',2);
SELECT w.first_name as worker, b.first_name as boss
FROM employee w, employee b
WHERE w.manager_id = b.employee_id</source>
<source lang='sql' class='def e-mysql'>CREATE TABLE employee(
employee_id INTEGER PRIMARY KEY,
first_name VARCHAR(10),
dept_code VARCHAR(10),
manager_id INTEGER REFERENCES employee);
INSERT INTO employee VALUES (1,'Robin','Eng',NULL);
INSERT INTO employee VALUES (2,'Jon','SoC',1);
INSERT INTO employee VALUES (3,'Andrew','SoC',2);
INSERT INTO employee VALUES (4,'Alison','SoC',2);
SELECT w.first_name as worker, b.first_name as boss
FROM employee w, employee b
WHERE w.manager_id = b.employee_id</source>
<source lang='sql' class='def e-sqlserver'>USE scott;
CREATE TABLE employee(
employee_id INTEGER PRIMARY KEY,
first_name VARCHAR(10),
dept_code VARCHAR(10),
manager_id INTEGER REFERENCES employee);
INSERT INTO employee VALUES (1,'Robin','Eng',NULL);
INSERT INTO employee VALUES (2,'Jon','SoC',1);
INSERT INTO employee VALUES (3,'Andrew','SoC',2);
INSERT INTO employee VALUES (4,'Alison','SoC',2);
SELECT w.first_name as worker, b.first_name as boss
FROM employee w, employee b
WHERE w.manager_id = b.employee_id</source>
<source lang='sql' class='def'>CREATE TABLE employee(
employee_id INTEGER PRIMARY KEY,
first_name VARCHAR(10),
dept_code VARCHAR(10),
manager_id INTEGER REFERENCES employee);
INSERT INTO employee VALUES (1,'Robin','Eng',NULL);
INSERT INTO employee VALUES (2,'Jon','SoC',1);
INSERT INTO employee VALUES (3,'Andrew','SoC',2);
INSERT INTO employee VALUES (4,'Alison','SoC',2);
SELECT w.first_name as worker, b.first_name as boss
FROM employee w, employee b
WHERE w.manager_id = b.employee_id
</source>
</div>

{{SELECT Ref}}

@@ Line 1: / Line 1: @@
 How do you use Equi Join to join two tables with the same name?
 <div class='ht'>
-<div class=params>schema:gisq</div>
+<div class=params>schema:scott</div>
 <div>
 I am trying to join two tables with the same name. I am required to use an inner join

@@ Line 1: / Line 1: @@
 How do you use Equi Join to join two tables with the same name?
 <div class='ht'>
 <div>
 I am trying to join two tables with the same name. I am required to use an inner join
@@ Line 14: / Line 15: @@
 does not show up in the results.
 </div>
-<source lang=sql class='tidy'>
+<source lang=sql class='tidy'>DROP TABLE employee</source>
 <source lang='sql' class='def e-access'>CREATE TABLE employee(
    employee_id INTEGER PRIMARY KEY,

Equi Join - Revision history

Marek at 13:21, 12 July 2012

Marek at 13:18, 12 July 2012

Marek: Created page with "How do you use Equi Join to join two tables with the same name? I am trying to join two tables with the same name. I am required to use an inner join Al..."

Marek: Created page with "How do you use Equi Join to join two tables with the same name?

I am trying to join two tables with the same name. I am required to use an inner join Al..."