Joining 4 tables together

[Mayoor]

Hi I need a view which will join 4 tables together by doing this...

CREATE VIEW dbo.submittedApril
AS
SELECT *
FROM applicants INNER JOIN
applicantData ON applicants.ID = applicantData.ID
UNION ALL
SELECT *
FROM applicants_inactive INNER JOIN
appData_inactive ON applicants_inactive.ID = appData_inactive.ID

However when I try to create this as a view in Query analyser

I get this error message.......

Server: Msg 8156, Level 16, State 1, Procedure submittedApril, Line 4
The column 'ID' was specified multiple times for 'submittedApril'.

Please can anyone help....

Mayoor

 

[dragonwell]

Instead of Select *, you will need to specify the columns. If one table's column name is the same as another table's (like the "ID"), use "AS" - for example:

SELECT ID AS ApplicantID, Column1, Column2, Column3
FROM applicants INNER JOIN
applicantData ON applicants.ID = applicantData.ID
UNION ALL
SELECT  ID AS InnactiveApplicantID, ColumnX, ColumnY, ColumnZ
FROM applicants_inactive INNER JOIN
appData_inactive ON applicants_inactive.ID = appData_inactive.ID

Hope that helps...

 

[Mayoor]

right what happens if all the columns in the first join are the same as the columns in the 2nd join. Will I have to alias all of them?

 

[MDXer]

unfortunately yes.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[Mayoor]

right well with that code im getting

SELECT ID AS ApplicantID
FROM applicants INNER JOIN
applicantData ON applicants.ID = applicantData.ID
UNION ALL
SELECT ID AS InnactiveApplicantID
FROM applicants_inactive INNER JOIN
appData_inactive ON applicants_inactive.ID = appData_inactive.ID

Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'ID'.


any ideas?

 

[SQLSister]

That error means it doesn't know which ID column to display. Specify one of the tables in the join as part of the field name.