I have 3 tables (janitor, security & manager)
which inherited from the user table
.
What is the query can be used to produce the following result(list all the name of the child tables)?
Thank you. Cheer 🙂
inheritanceMySQL
I have 3 tables (janitor, security & manager)
which inherited from the user table
.
What is the query can be used to produce the following result(list all the name of the child tables)?
Thank you. Cheer 🙂
As long as the set of inherited column values is unique across all tables, there is a simple solution with a NATURAL
join (one of the rare use cases for this clause!):
SELECT * FROM ONLY parent NATURAL FULL JOIN child;
Since NATURAL
is (per documentation):
... shorthand for a
USING
list that mentions all columns in the two tables that have the same names.
You get each column only once without explicit SELECT
list, and rows from the parent table are extended with NULL values for added columns - exactly the way you desire.
This even works with NULL values in any of the columns.
To also see the origin of each row in the result, insert another row to demonstrate the difference:
INSERT INTO child(col1) VALUES(1);
Then:
SELECT COALESCE(p.tableoid, c.tableoid)::regclass AS tbl, *
FROM ONLY parent p NATURAL FULL JOIN child c;
tbl | col1 | col2
-------+------+----
parent | 1 | NULL
child | 1 | NULL
child | 2 | 2
SQL Fiddle with more test rows.
You can join to multiple child tables this way if all additional column names in all child tables are unique. Else you'll have to spell out an explicit SELECT
list and explicit join conditions.
The set of inherited column values has to be unique across all child tables.
All of this could have been accomplished with the INFORMATION_SCHEMA database
The table names are in the INFORMATION_SCHEMA
SELECT table_name FROM information_schema.tables
WHERE table_schema='conntrack';
You could then line up the tables that exist against the tabidx
table
SELECT B.table_name FROM (SELECT date FROM tabidx) A
INNER JOIN (SELECT table_name FROM information_schema.tables
WHERE table_schema='conntrack') B ON A.date = B.table_name;
With these table_names, setup a long query against all tables in conntrack
SET @fromdate = '2015-04-25';
SET @todate = CURRENT_DATE();
SET @given_username='L001000';
SELECT
GROUP_CONCAT(qry SEPARATOR ' UNION ')
INTO @sql
FROM
(
SELECT CONCAT('SELECT ',QUOTE(table_name),' as "Date and Time"',
',username,''%'' srcip,''%'' scport FROM `',table_name,
'` WHERE username=',QUOTE(@given_username)) qry
FROM
(
SELECT BB.table_name FROM
(
SELECT date FROM tabidx
WHERE date>=@fromdate AND date<=@todate
) AA
INNER JOIN
(
SELECT table_name FROM information_schema.tables
WHERE table_schema='conntrack'
) BB ON AA.date = BB.table_name
) A
) B;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
DROP DATABASE IF EXISTS conntrack_sample;
CREATE DATABASE conntrack_sample;
USE conntrack_s
CREATE TABLE tabidx (date date, PRIMARY KEY (date));
INSERT INTO tabidx VALUES
('2015-04-25'),('2015-04-26'),('2015-04-27'),
('2015-04-28'),('2015-04-29');
SELECT * FROM tabidx;
CREATE TABLE `0000-00-00`
(
time time,username CHAR(7),
srcip CHAR(15),scport INT,
PRIMARY KEY (username,time)
);
CREATE TABLE `2015-04-25` LIKE `0000-00-00`;
CREATE TABLE `2015-04-26` LIKE `0000-00-00`;
CREATE TABLE `2015-04-27` LIKE `0000-00-00`;
CREATE TABLE `2015-04-28` LIKE `0000-00-00`;
CREATE TABLE `2015-04-29` LIKE `0000-00-00`;
INSERT INTO `2015-04-25` VALUES
('19:20:00','L001000','10.10.10.1',1304),
('19:20:00','L001001','10.10.10.2',1640),
('19:20:01','L001002','10.10.10.3',2001);
INSERT INTO `2015-04-26` VALUES
('19:21:00','L001000','10.10.10.1',1304),
('19:21:00','L001001','10.10.10.2',1640),
('19:21:01','L001002','10.10.10.3',2001);
mysql> DROP DATABASE IF EXISTS conntrack_sample;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE DATABASE conntrack_sample;
Query OK, 1 row affected (0.00 sec)
mysql> USE conntrack_sample
Database changed
mysql> CREATE TABLE tabidx (date date, PRIMARY KEY (date));
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO tabidx VALUES
-> ('2015-04-25'),('2015-04-26'),('2015-04-27'),
-> ('2015-04-28'),('2015-04-29');
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM tabidx;
+------------+
| date |
+------------+
| 2015-04-25 |
| 2015-04-26 |
| 2015-04-27 |
| 2015-04-28 |
| 2015-04-29 |
+------------+
5 rows in set (0.00 sec)
mysql> CREATE TABLE `0000-00-00`
-> (
-> time time,username CHAR(7),
-> srcip CHAR(15),scport INT,
-> PRIMARY KEY (username,time)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE `2015-04-25` LIKE `0000-00-00`;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE `2015-04-26` LIKE `0000-00-00`;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE `2015-04-27` LIKE `0000-00-00`;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE `2015-04-28` LIKE `0000-00-00`;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE `2015-04-29` LIKE `0000-00-00`;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO `2015-04-25` VALUES
-> ('19:20:00','L001000','10.10.10.1',1304),
-> ('19:20:00','L001001','10.10.10.2',1640),
-> ('19:20:01','L001002','10.10.10.3',2001);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `2015-04-26` VALUES
-> ('19:21:00','L001000','10.10.10.1',1304),
-> ('19:21:00','L001001','10.10.10.2',1640),
-> ('19:21:01','L001002','10.10.10.3',2001);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> SET @fromdate = '2015-04-25';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @todate = CURRENT_DATE();
Query OK, 0 rows affected (0.02 sec)
mysql> SET @given_username='L001000';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT
-> GROUP_CONCAT(qry SEPARATOR ' UNION ')
-> INTO @sql
-> FROM
-> (
-> SELECT CONCAT('SELECT ',QUOTE(table_name),' as "Date and Time"',
-> ',username,''%'' srcip,''%'' scport FROM `',table_name,
-> '` WHERE username=',QUOTE(@given_username)) qry
-> FROM
-> (
-> SELECT BB.table_name FROM
-> (
-> SELECT date FROM tabidx
-> WHERE date>=@fromdate AND date<=@todate
-> ) AA
-> INNER JOIN
-> (
-> SELECT table_name FROM information_schema.tables
-> WHERE table_schema='conntrack_sample'
-> ) BB ON AA.date = BB.table_name
-> ) A
-> ) B;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql>
mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
+---------------+----------+-------+--------+
| Date and Time | username | srcip | scport |
+---------------+----------+-------+--------+
| 2015-04-25 | L001000 | % | % |
| 2015-04-26 | L001000 | % | % |
+---------------+----------+-------+--------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
NOTE: Here is what @sql has
mysql> SELECT @sql\G
*************************** 1. row ***************************
@sql: SELECT '2015-04-25' as "Date and Time",username,'%' srcip,'%' scport FROM `2015-04-25` WHERE username='L001000' UNION SELECT '2015-04-26' as "Date and Time",username,'%' srcip,'%' scport FROM `2015-04-26` WHERE username='L001000' UNION SELECT '2015-04-27' as "Date and Time",username,'%' srcip,'%' scport FROM `2015-04-27` WHERE username='L001000' UNION SELECT '2015-04-28' as "Date and Time",username,'%' srcip,'%' scport FROM `2015-04-28` WHERE username='L001000' UNION SELECT '2015-04-29' as "Date and Time",username,'%' srcip,'%' scport FROM `2015-04-29` WHERE username='L001000'
1 row in set (0.00 sec)
mysql>
Best Answer
Try this:
This will list of referenced tables under table
'user'
.