Try this:
CREATE OR REPLACE VIEW person_phones (id, NAME, Celphone#, Homephone#) AS
SELECT t1.id AS id, t1.NAME AS NAME, t2.Phnumber AS Celphone#,
t3.Phnumber AS Homephone#
FROM person t1, phone t2, phone t3
WHERE t1.cellphonID = t2.id
AND t1.homePhonID = t3.id
WITH CHECK OPTION;
Hope this helps.
Michael Serbanescu
geetha_sanjeeviah_at_p-com.com wrote:
>
> Hi ;
>
> I have a question about views using multiple tables.
>
> I have a table called person table:
>
> It has the following fields.
>
> id PRIMARYKEY
> NAME NOT NULL
> cellphonID FOREIGNKEY REFERENCES PHONE(ID);
> homePhonID FOREIGN KEY REFERENCES PHONE(ID);
>
> The table phone has the following:
>
> id PRIMARY KEY
> Phnumber NOT NULL
>
> I want to create a view using the above so that I can have the name, and
> both the telephone numbers for that person.
>
> How do I write my select statement?.
>
> Please direct all the responses to geetha_sanjeeviah_at_p-com.com.
>
> Thanks
> Gita
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Thu Feb 26 1998 - 00:00:00 CST
