how to structure this view statement, not sure how

[antonyx]

ok here is a question. to my knowledge this is how you do a view in sql, im using oracle.

CREATE VIEW myviewname
SELECT the fields i want to show
FROM the tables they are in
WHERE something equals something

imagine this scenario:
i have 3 tables. a table holding details of a campaign such as start date, end date campaign id... a table holding details of the campaign team (who is working on it, using staffids and which campaign it's for using campaignid) and a table with the staff details(including the staffid and name surname...)

My tables are made and the primary keys are set.
the tables are linked like this
CAMPAIGN to CTEAM using CampaignId
CTEAM to STAFF using StaffId


CAMPAIGN table
CampaignName,StartDate,EndDate

CTEAM table
CManager, Contact1, Contact2, PurchasingAst

STAFF table
Forname,Surname,Position,TelNumber

what i need to know is how to structure the view in order to show details from all the dif tables?

 

[PHV]

Something like this ?
CREATE VIEW myviewname
AS SELECT ...
FROM CAMPAIGN
INNER JOIN CTEAM ON ...
INNER JOIN STAFF ON ...


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[antonyx]

ok would something like this work

CREATE VIEW staff_camp

AS SELECT CampaignName,StartDate,EndDate,CManager, Contact1,Contact2,PurchasingAst,Forname,Surname,Position,TelNumber

FROM CAMPAIGN,CTEAM,STAFF

well. as i am fairly novice to sql, im not to sure what that inner join means. if you look at what i have wrote in this reply, its the essence of how i want the view to look. im sure that format is wrong however. im not sure how to select multiple fields from multiple tables in one view, and also i dont know how to state in the view that the tables are linked using my respective ids.