Display column from multiple tables

问题: So I have 4 tables and there are some duplicate columns, and I want to select columns from each table and display every single column from the 4 tables. system table +-...

问题:

So I have 4 tables and there are some duplicate columns, and I want to select columns from each table and display every single column from the 4 tables.

system table

+------+-------+----------+-------+
| reg  |   ic  |    plate |  type |
+------+-------+----------+-------+
| 1000 | 22222 | WWW123   | car   |
| 1001 | 11111 | BBB987   | truck |
+------+-------+----------+-------+

owner table

+------+-------+
| name |  ic   |
+------+-------+
| john |  2222 |
| joe  |  1111 |
+------+-------+

car table

+--------+-------+------+-------+-------+----------+
| plate  | color | year | make  | model | capacity |
+--------+-------+------+-------+-------+----------+
| WWW123 | blue  | 2015 | Honda | City  |      1.5 |
+--------+-------+------+-------+-------+----------+

truck table

+--------+-------+------+--------+-------+----------+---------+
| plate  | color | year |  make  | model | capacity | maxload |
+--------+-------+------+--------+-------+----------+---------+
| BBB987 | red   | 2018 | Toyota | Hilux |      2.0 |    3000 |
+--------+-------+------+--------+-------+----------+---------+

Let's say I want to display every data in this way:

+------+------+-------+--------+-------+------+--------+-------+----------+-------+---------+
| reg  | name |  ic   | plate  | color | year |  make  | model | capacity | type  | maxload |
+------+------+-------+--------+-------+------+--------+-------+----------+-------+---------+
| 1000 | John | 22222 | WWW123 | blue  | 2015 | Honda  | City  |      1.5 | car   |         |
| 1001 | Joe  | 11111 | BBB987 | red   | 2018 | Toyota | Hilux |      2.0 | truck |    3000 |
+------+------+-------+--------+-------+------+--------+-------+----------+-------+---------+

Is there any possible way to generate SQL that does this?


回答1:

You can try below -

select reg,name,A.ic,A.plate,color,year,make,model,capacity,type,maxload 
from systemtable A
inner join ownertable B on A.ic=B.ic
join
(
select plate, color ,year ,make ,model, capacity,null as maxload from car
union all
select plate, color ,year ,make ,model, capacity,maxload from truck
)C on A.plate=C.plate

回答2:

This is a case for join statements. Since you want to find the intersection between the tables, we are going to use UNION ALL + an inner join (selecting values matching in multiple tables).

SELECT name, ic, req, plate, year, color, make, model, capacity, maxload 
FROM ((SELECT * FROM car c WHERE c.plate=plate) UNION ALL (SELECT * FROM truck AS t WHERE t.plate=plate))
INNER JOIN owner AS o ON o.ic=ic

Try to execute it. You might need to specify every field from car and truck instead of using the asterix (*)

/AF


回答3:

Similar to other answers. But here you go.

Table Structure & Data

 CREATE TABLE systemtbl
  (
     reg   INT,
     ic    INT,
     plate VARCHAR(255),
     type  VARCHAR(255)
  );

CREATE TABLE ownertbl
  (
     name VARCHAR(255),
     ic   INT
  );

CREATE TABLE cartbl
  (
     plate    VARCHAR(255),
     color    VARCHAR(255),
     year     INT,
     make     VARCHAR(255),
     model    VARCHAR(255),
     capacity DECIMAL(2, 1)
  );

CREATE TABLE trucktbl
  (
     plate    VARCHAR(255),
     color    VARCHAR(255),
     year     INT,
     make     VARCHAR(255),
     model    VARCHAR(255),
     capacity DECIMAL(2, 1),
     maxload  INT
  );

INSERT INTO systemtbl
VALUES      (1000, 22222,'WWW123', 'car'),
            (1001, 11111, 'BBB987', 'truck');

INSERT INTO ownertbl
VALUES      ('john', 22222),
            ('joe', 11111);

INSERT INTO cartbl
VALUES      ('WWW123', 'blue', 2015, 'Honda', 'City', 1.5);

INSERT INTO trucktbl
VALUES      ('BBB987', 'red', 2018, 'Toyota', 'Hilux', 2.0, 3000 );  

SQL Query

SELECT systemtbl.reg,
       ownertbl.name,
       systemtbl.ic,
       systemtbl.plate,
       systemtbl.type,
       tmp.color,
       tmp.year,
       tmp.make,
       tmp.model,
       tmp.capacity,
       tmp.maxload
FROM   (SELECT plate, color, year, make, model, capacity, NULL AS maxload
        FROM   cartbl
        UNION ALL
        SELECT plate, color, year, make, model, capacity, maxload
        FROM   trucktbl) tmp
       INNER JOIN systemtbl USING (plate)
       INNER JOIN ownertbl USING (ic)

Output

+-------+-------+--------+---------+--------+--------+-------+---------+--------+-----------+---------+
| reg   | name  |  ic    | plate   | type   | color  | year  |  make   | model  | capacity  | maxload |
+-------+-------+--------+---------+--------+--------+-------+---------+--------+-----------+---------+
| 1000  | john  | 22222  | WWW123  | car    | blue   | 2015  | Honda   | City   |      1.5  | (null)  |
| 1001  | joe   | 11111  | BBB987  | truck  | red    | 2018  | Toyota  | Hilux  |        2  | 3000    |
+-------+-------+--------+---------+--------+--------+-------+---------+--------+-----------+---------+

Online Demo: http://sqlfiddle.com/#!9/52596e/3/0

  • 发表于 2019-03-17 06:51
  • 阅读 ( 98 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除