SQL Join 2表 [英] SQL Join 2 tables

问题描述

我有两个表，一个名为 Person ，其中包含列 ID 和 Name ，第二个表名为 Relation ，其中包含两列，每列包含一个 Person 的 ID .这是关于客户和军人之间的关系.我想加入这两个表，以便在每个关系中都有人的名字.是否可以通过某种联接来编写此查询?

:

我必须做错了什么，但是没有用.我尝试了很多形式的查找查询，但是我只遇到了一列或一些错误.这实际上是学校的任务，我已经完成了(使用不同的JOIN查询).首先，我一直试图这样做，但是我失败了:这似乎是非常普遍的情况，所以我不知道为什么它对我来说太复杂了.

这是我的桌子:

  CREATE TABLE Oprava ..(修复)(KodPodvozku整数主要参考文献Automobil(KodPodvozku)，IDzakaznika ..(IDcustomer)整数参考Osoba(ID)，IDzamestnance ..(IDemployee)整数参考Osoba(ID));创建表Osoba ..(人)(ID整数约束primaryKeyOsoba主键，Jmeno ..(Name)VARCHAR(256)NOT NULL，OP INTEGER UNIQUE NOT NULL); 

它是捷克语，但".."后面方括号中的单词与英语等效.PS:我正在使用Oracle SQL.

解决方案

假设您的表是:

 人员:(id，姓名)关系:(customer_id，serviceman_id) 

使用标准SQL:

  SELECT p1.name AS客户名称，p2.name AS serviceman_name从人p1p1.id = relations.customer_id上的JOIN关系加入人员p2的关系.serviceman_d= p2.id; 

进一步的解释

联接将创建下表:

  p1.id | p1.name | relations.customer_id | relations.serviceman_id | p2.id | p2.name 

其中 p1.id = relations.customer_id 和 p2.id = relations.serviceman_id . SELECT 子句仅从 JOIN 中选择名称.

请注意，如果 relations 中的所有id都在 person 中，则结果大小将恰好是 relations 表的大小.您可能需要添加外键以进行验证.

I have two tables one named Person, which contains columns ID and Name and the second one, named Relation, which contains two columns, each of which contains an ID of a Person. It's about a relation between customer and serviceman. I'd like to Join these two tables so that I'll have names of people in every relation. Is it possible to write this query with some kind of joining?

EDIT::

I must do something wrong, but it's not working. I had tried a lot of forms of so looking queries, but I had been only getting one column or some errors. It's actually the school task, I have it already done (with different JOIN query). Firstly I had been trying to do this, but I'd failed: It seems to be very common situation, so I don't know why it's too complicated for me..

Here are my tables:

CREATE TABLE Oprava..(Repair) (  
  KodPodvozku INTEGER PRIMARY KEY REFERENCES Automobil(KodPodvozku),  
  IDzakaznika..(IDcustomer) INTEGER REFERENCES Osoba(ID),  
  IDzamestnance..(IDemployee) INTEGER REFERENCES Osoba(ID)  
);

CREATE TABLE Osoba..(Person) (  
  ID INTEGER CONSTRAINT primaryKeyOsoba PRIMARY KEY ,  
  Jmeno..(Name) VARCHAR(256) NOT NULL,  
  OP INTEGER UNIQUE NOT NULL  
);

It's in Czech, but the words in brackets after ".." are english equivalents. PS: I am using Oracle SQL.

解决方案

Assuming your tables are:

persons:   (id, name)
relations: (customer_id, serviceman_id)

Using standard SQL:

SELECT p1.name AS customer_name, 
       p2.name AS serviceman_name
FROM persons p1 
     JOIN relations ON p1.id=relations.customer_id
     JOIN persons p2 ON relations.serviceman_d=p2.id;

Further explanation

The join creates the following table:

p1.id|p1.name|relations.customer_id|relations.serviceman_id|p2.id|p2.name

Where p1.id=relations.customer_id, and p2.id=relations.serviceman_id. The SELECT clause chooses only the names from the JOIN.

Note that if all the ids from relations are also in persons, the result size would be exactly the size of the relations table. You might want to add a foreign key to verify that.

这篇关于SQL Join 2表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！