Click here to Skip to main content
14,423,437 members
Rate this:
Please Sign up or sign in to vote.
See more:
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'
Posted
Updated 8-Jan-18 13:31pm
v3
Comments
ZurdoDev 8-Jan-18 8:15am
   
What's the problem with what you have?

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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'


Note, how I used different aliases for the duplicated listname column:
e.listname as select_listname,
f.listname as reject_listname
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100