如何使用两个以上的表连接查询创建视图表 [英] How can I create a view table using more then two tables join queries

问题描述

我正在编写一个查询来显示数据，我想创建一个包含查询数据的表，在运行此查询来创建表时出错，你能不能建议我创建一个表的最佳方法查看表使用此查询

I am writing a query to display the data and i want to create a table which is contains the query data, have a error occur while running this query to crate a table, can you anyone suggest me the best way to create a view table using this query

SELECT 
	a.*,
	b.fullname,
	b.lastname,
	c.*,
	d.*,
	e.*,
	f.* 
FROM 
	educationaldetails as a 
JOIN registration as b on a.hiremee_id=b.hiremee_id 
JOIN assessment as c on a.hiremee_id=c.hiremee_id 
JOIN userresource as d on a.hiremee_id=d.hiremee_id 
JOIN candidateselectionlist as e on a.hiremee_id=e.hiremee_id 
JOIN candidaterejectionlist as f on a.hiremee_id=f.hiremee_id 
where b.status='active'

我有什么试过：

What I have tried:

SELECT 
	a.*,
	b.fullname,
	b.lastname,
	c.*,
	d.*,
	e.*,
	f.* 
FROM 
	educationaldetails as a 
JOIN registration as b on a.hiremee_id=b.hiremee_id 
JOIN assessment as c on a.hiremee_id=c.hiremee_id 
JOIN userresource as d on a.hiremee_id=d.hiremee_id 
JOIN candidateselectionlist as e on a.hiremee_id=e.hiremee_id 
JOIN candidaterejectionlist as f on a.hiremee_id=f.hiremee_id 
where b.status='active'

推荐答案

您的查询的问题是它不能用于创建视图或表。原因是表中存在具有相同名称的列。例如，你有所有表格中的hiremee_id列。



当你使用。*，c。*，d。*，e。*，f 。*在您的查询中，重复的列将被列出多次。虽然可以在正常的select语句中用于显示目的，但是不能用作视图或CREATE TABLE AS。



你需要什么在列表中列出您想要的列，如果有重复的名称，则使用别名，如下所示：

The problem with your query is that it cannot be used as is to create a view or a table. The reason for this is that there are columns with the same name that exist in your tables. E.g you have the hiremee_id column that exists in all of your tables.

When you use a.*, c.*, d.*, e.*, f.* in your query the duplicate columns will be listed several times. While it is ok to use in normal select statement for display purposes, it is not possible to use as a view or as a CREATE TABLE AS.

What you need to do is to list the columns you want in your view, and if there are duplicate names then use the aliases, like this:

SELECT 
	a.hiremee_id,
	b.fullname,
	b.lastname,
	c.score,
	c.rank,
	d.source,
	e.listname as select_listname,
	f.listname as reject_listname
FROM 
	educationaldetails as a 
JOIN registration as b on a.hiremee_id=b.hiremee_id 
JOIN assessment as c on a.hiremee_id=c.hiremee_id 
JOIN userresource as d on a.hiremee_id=d.hiremee_id 
JOIN candidateselectionlist as e on a.hiremee_id=e.hiremee_id 
JOIN candidaterejectionlist as f on a.hiremee_id=f.hiremee_id 
where b.status='active'

注意，我如何使用不同的别名对于重复的listname列：

Note, how I used different aliases for the duplicated listname column:

e.listname as select_listname,
f.listname as reject_listname

这篇关于如何使用两个以上的表连接查询创建视图表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！