Linking three tables or more - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Linking three tables or more

Author

Topic

mawan
Starting Member

4 Posts

Posted - 2007-07-02 : 10:25:29

I'm begginer in MS SQL Server 2000.
Assume I have three tables.


1. Table name: animal
   code | name    | habitat
   =====+=========+========
   A01  | Fish    | Sea
   A02  | Bird    | Forest

2. Table name: human
   code | name    | hobby
   =====+=========+======
   H01  | John    | Sing
   H02  | Doe     | Swim

3. Table name: flower
   code | name    | color
   =====+=========+======
   F01  | Rose    | red
   F02  | Jasmine | White

I access that tables using PHP. I want the syntax select is like this:

select code, name from newtable

The output must like this:


   code | name
   =====+=========
   A01  | Fish
   A02  | Bird
   H01  | John
   H02  | Doe
   F01  | Rose
   F02  | Jasmine

If I update a data on table animal, then this update automatically appear on the newtable. How to create this? Must I use view, stored procedure, or something else? Please teach me step by step. Thank you.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-02 : 10:29:01

You can create a view if you like, which unions data from all three table and then you can just select from that view.

Or:

Select * from
(
Select code, name from animal
union all
Select code, name from human
union all
Select code, name from flower
) t

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources