Combine tables - Revision history

Connor at 15:12, 2 August 2012

2012-08-02T15:12:06Z

Connor at 13:36, 2 August 2012

2012-08-02T13:36:44Z

Marek at 15:16, 1 August 2012

2012-08-01T15:16:13Z

Marek at 15:15, 1 August 2012

2012-08-01T15:15:09Z

Marek at 15:13, 1 August 2012

2012-08-01T15:13:17Z

Connor at 15:09, 30 July 2012

2012-07-30T15:09:55Z

Connor at 14:40, 30 July 2012

2012-07-30T14:40:39Z

Connor: Created page with "Combine tables containing different data

schema:scott
DROP TABLE staff; DROP TABLE student;
2012-07-30T14:37:24Z

Created page with "Combine tables containing different data <div class='ht'> <div class=params>schema:scott</div> <source lang=sql class='tidy'>DROP TABLE staff; DROP TABLE student;</source> <so..."

New page
Combine tables containing different data
<div class='ht'>
<div class=params>schema:scott</div>
<source lang=sql class='tidy'>DROP TABLE staff;
DROP TABLE student;</source>
<source lang=sql class='setup'> CREATE TABLE staff(
staffid INTEGER,
email VARCHAR(20),
name VARCHAR(20),
salary INTEGER );
INSERT INTO staff VALUES (0173,stan@bos.edu,Stern','Stan,99','000);
INSERT INTO staff VALUES (0101,ali@bos.edu,Aloof','Alison,30','000);
CREATE TABLE student (
id INTEGER,
fname VARCHAR(20),
lname VARCHAR(20),
gpa INTEGER );
INSERT INTO staff VALUES (1007,Peter,Perfect,590);
INSERT INTO staff VALUES (1008,Donald,Dunce,220);
</source>
<div>
<p>You can use UNION when you have two tables you want to combine
but that contain different data.</p>
<p>You will have to make the two tables agree before you can do the UNION though</p>
<p>In this example a staff table and a student table are combined</p>
</div>
<source lang='sql' class='def e-oracle'>SELECT 'F' || staffid id, name name,
email email, salary salary, NULL gpa, 'Staff' species
FROM staff
UNION
SELECT 'S' || id id, lname || ',' || fname name,
id || '@bos.edu' email, NULL salary, gpa gpa,
'Student' species
FROM student</source>
<source lang='sql' class='def e-postgres'>SELECT 'F' || staffid id, name name,
email email, salary salary, NULL gpa, 'Staff' species
FROM staff
UNION
SELECT 'S' || id id, lname || ',' || fname name,
id || '@bos.edu' email, NULL salary, gpa gpa,
'Student' species
FROM student</source>
<source lang='sql' class='def e-mysql'>SELECT CONCAT('F',staffid) id, name name,
email email, salary salary, NULL gpa, 'Staff' species
FROM staff
UNION
SELECT CONCAT('S',id) id, CONCAT(lname,',',fname) name,
CONCAT(id,'@bos.edu') email, NULL salary, gpa gpa,
'Student' species
FROM student</source>
<source lang='sql' class='def e-sqlserver'>SELECT 'F' + staffid id, name name,
email email, salary salary, NULL gpa, 'Staff' species
FROM staff
UNION
SELECT 'S' + id id, lname + ',' + fname name,
id + '@bos.edu' email, NULL salary, gpa gpa,
'Student' species
FROM student</source>
<source lang='sql' class='def e-access'>SELECT 'F' + staffid id, name name,
email email, salary salary, NULL gpa, 'Staff' species
FROM staff
UNION
SELECT 'S' + id id, lname + ',' + fname name,
id + '@bos.edu' email, NULL salary, gpa gpa,
'Student' species
FROM student</source>
<div class="ecomm e-mysql" style="display: none"></div>
</div>

{{Hacks Ref}}

@@ Line 1: / Line 1: @@
-Combine tables containing different data
+<p>Combine tables containing different data.</p>
 <div class='ht'>
 <div class=params>schema:scott</div>
@@ Line 22: / Line 47: @@
 <p>You can use <code>UNION</code> when you have two tables you want to combine
 but that contain different data.</p>
-<p>You will have to make the two tables agree before you can do the <code>UNION</code> though</p>
+<p>You will have to make the two tables agree before you can do the <code>UNION</code> though, this is done by making the
-<p>In this example a staff table and a student table are combined</p>
+final table contain all information from all tables with <code>NULL</code> entries in the rows that don't
 </div>
 <source lang='sql' class='def e-oracle'>SELECT 'F' || staffid id, name name,

@@ Line 9: / Line 9: @@
    name VARCHAR(20),
    salary INTEGER );
-INSERT INTO staff VALUES (0173,'stan@bos.edu','Stern','Stan',99000);
+INSERT INTO staff VALUES (0173,'stan@bos.edu','Stern, Stan',99000);
-INSERT INTO staff VALUES (0101,'ali@bos.edu','Aloof','Alison',30000);
+INSERT INTO staff VALUES (0101,'ali@bos.edu','Aloof, Alison',30000);
 CREATE TABLE student (
    id INTEGER,

@@ Line 9: / Line 9: @@
    name VARCHAR(20),
    salary INTEGER );
-INSERT INTO staff VALUES (0173,'stan@bos.edu','Stern','Stan,99','000);
+INSERT INTO staff VALUES (0173,'stan@bos.edu','Stern','Stan',99000);
-INSERT INTO staff VALUES (0101,'ali@bos.edu','Aloof','Alison,30','000);
+INSERT INTO staff VALUES (0101,'ali@bos.edu','Aloof','Alison',30000);
 CREATE TABLE student (
    id INTEGER,

@@ Line 52: / Line 52: @@
 <p>In this example a staff table and a student table are combined.</p>
 </div>
-<source lang='sql' class='def e-oracle'>SELECT 'F' || staffid id, name name,
+<source lang='sql' class='def e-oracle'>
 email email, salary salary, NULL gpa, 'Staff' species
-FROM staff
+  FROM staff
-UNION
+  UNION
-SELECT 'S' || id id, lname || ',' || fname name,
+  SELECT 'S' || id id, lname || ',' || fname name,
 id || '@bos.edu' email, NULL salary, gpa gpa,
 'Student' species
-FROM student</source>
+    FROM student</source>
-<source lang='sql' class='def e-postgres'>SELECT 'F' || staffid id, name name,
+<source lang='sql' class='def e-postgres'>
 email email, salary salary, NULL gpa, 'Staff' species
-FROM staff
+  FROM staff
-UNION
+  UNION
-SELECT 'S' || id id, lname || ',' || fname name,
+  SELECT 'S' || id id, lname || ',' || fname name,
 id || '@bos.edu' email, NULL salary, gpa gpa,
 'Student' species
-FROM student</source>
+    FROM student</source>
-<source lang='sql' class='def e-mysql'>SELECT CONCAT('F',staffid) id, name name,
+<source lang='sql' class='def e-mysql'>
 email email, salary salary, NULL gpa, 'Staff' species
-FROM staff
+  FROM staff
-UNION
+  UNION
-SELECT CONCAT('S',id) id, CONCAT(lname,',',fname) name,
+  SELECT CONCAT('S',id) id, CONCAT(lname,',',fname) name,
 CONCAT(id,'@bos.edu') email, NULL salary, gpa gpa,
 'Student' species
-FROM student</source>
+    FROM student</source>
-<source lang='sql' class='def e-sqlserver'>SELECT 'F' + staffid id, name name,
+<source lang='sql' class='def e-sqlserver'>
 email email, salary salary, NULL gpa, 'Staff' species
-FROM staff
+  FROM staff
-UNION
+  UNION
-SELECT 'S' + id id, lname + ',' + fname name,
+  SELECT 'S' + id id, lname + ',' + fname name,
 id + '@bos.edu' email, NULL salary, gpa gpa,
 'Student' species
-FROM student</source>
+    FROM student</source>
-<source lang='sql' class='def e-access'>SELECT 'F' + staffid id, name name,
+<source lang='sql' class='def e-access'>
 email email, salary salary, NULL gpa, 'Staff' species
-FROM staff
+  FROM staff
-UNION
+  UNION
-SELECT 'S' + id id, lname + ',' + fname name,
+  SELECT 'S' + id id, lname + ',' + fname name,
 id + '@bos.edu' email, NULL salary, gpa gpa,
 'Student' species
-FROM student</source>
+   FROM student</source>
 <div class="ecomm e-mysql" style="display: none"></div>
 </div>
 {{Hacks Ref}}

@@ Line 9: / Line 9: @@
    name VARCHAR(20),
    salary INTEGER );
-INSERT INTO staff VALUES (0173,stan@bos.edu,Stern','Stan,99','000);
+INSERT INTO staff VALUES (0173,'stan@bos.edu','Stern','Stan,99','000);
-INSERT INTO staff VALUES (0101,ali@bos.edu,Aloof','Alison,30','000);
+INSERT INTO staff VALUES (0101,'ali@bos.edu','Aloof','Alison,30','000);
 CREATE TABLE student (
    id INTEGER,
@@ Line 16: / Line 16: @@
    lname VARCHAR(20),
    gpa INTEGER );
-INSERT INTO student VALUES (1007,Peter,Perfect,590);
+INSERT INTO student VALUES (1007,'Peter','Perfect',590);
-INSERT INTO student VALUES (1008,Donald,Dunce,220);
+INSERT INTO student VALUES (1008,'Donald','Dunce',220);
 </source>
 <div>