Creating a new VIEW (SQL SErver)

Question

Post reply

Creating a new VIEW (SQL SErver)

Affu Q-387175

SSC Veteran

Points: 224
More actions
December 4, 2006 at 9:51 am

#193299

I have the following tables:
Candidates Table: c. CandidateID, C. FirstName, c. LastName, c. Email, c. Nationality, c. English, c. Spanish, c. French, c. Portugese, c. OtherLanguage, c. OtherLanguageLevel, c. DOB
PandC Table: a.ID, a. PostingID, a. CandidateID, a. CategoryType
Postings Table: p. PostingID
Education Table: e. IDEduc, e. CandidateID, e. DegreeID
Degrees Table: d. Id, d. Degree, d. DegreeLevel
CandidateExperience Table: x. cid, x. exp
where c.CandiadteID=a.CandidateID=e.CandidateID=x.cid
where a.PostingID=p.PostingID
where e.DegreeID=d.ID
I need to create a view using joins or any appropriate method to get all the values for above said columns based on CandiadateID.
i.e. for each CandidateID, I need the FirstName, LastName, Email, Nationality, English, Spanish, French, Portugese, OtherLanguage, OtherLanguageLevel, DOB, PostingID, CategoryType, DegreeID, Degree, exp
Thanks in advance
Affu Q

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

Sreejith Sreedharan

SSCertifiable

Points: 5403

December 4, 2006 at 10:47 am

#675542

I don't see a link between Candidate and Education.

Affu Q-387175 SSC Veteran Points: 224 More actions · Answer 2

Candidates Table: c. CandidateID, C. FirstName, c. LastName, c. Email, c. Nationality, c. English, c. Spanish, c. French, c. Portugese, c. OtherLanguage, c. OtherLanguageLevel, c. DOB

Education Table: e. IDEduc, e. CandidateID, e. DegreeID

where c.CandidateID=e.CandidateID

Thanks for your time.

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

If your question is really about syntax, then the best way you can learn is to create the view via Enterprise Manager - right click "views".."new view"...and the wizard will walk you through the rest...

The best part about all this is that as you're creating the view you get to see the generated sql script for the same...it's a great learning tool!

**ASCII stupid question, get a stupid ANSI !!!**

Affu Q-387175 SSC Veteran Points: 224 More actions · Answer 4

Thanks Sushila.

I will give that a try and get back to you.

Affu Q-387175 SSC Veteran Points: 224 More actions · Answer 5

SELECT distinct c.CandidateID, c.FirstName, c.LastName, c.Email, c.Nationality, c.English, c.Spanish, c. French, c. Portuguese, c. OtherLanguage, c. OtherLanguageLevel, c. DOB, a.PostingID, a.CategoryType, e.DegreeID, d.Degree, x.exp

FROM Candidates c

INNER JOIN Pandc a ON c.CandidateID = a.CandidateID

INNER JOIN Postings p ON a.PostingID = p.postingID

INNER JOIN Education e ON c.CandidateID = e.CandidateID

INNER JOIN Degrees d ON e.degreeID = d.ID

INNER JOIN CandidateExperience x ON c.CandidateID = x.cid

It worked like a charm.

Thanks again for all your help.

Affu Q

P.S. If you think this is wrong in some way, please let me know.