How can I create a view table using more then two tables join queries

Question

0.00/5 (No votes)

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

SQL

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:

SQL

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 7-Jan-18 20:31pm

vengateshwaran

Updated 8-Jan-18 12:31pm

David_Wimbley

v3

Add a Solution

Comments

ZurdoDev 8-Jan-18 8:15am

What's the problem with what you have?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

chaau · Answer 1 · 2018-01-08T12:30:00

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