Creating views from 3 tables containing 1T1 and 1TM relationships

Question

There are three tables named as , main , vehicles , categories.

same primery key one -to -one relationship is made btw main and vehicles as field primery key MAINID in both tables. while one to many is made in main and categories tables , main contains CATID of that category table primery key.

Now i want to to get all the values from MAINID in such a way that it shows all the fields of main and vehicle for that respective "MAINID" key , and also select the category table row for that respective CATID present in selected MAINID(main tbl) row.

How i can create a view for this.

thanks

Answer 1

Sorry ,untested

SELECT <columns> FROM Main M JOIN Vehicles V ON M.mainid=V,mainid  JOIN Categories C ON M.Catid=C.Catid

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Are you looking for something like this?

SELECT m.*,v.*,c.*
FROM main m
INNER JOIN vehicles v ON m.MAINID = v.MAINID
INNER JOIN categories c ON m.CATID=c.CATID

Please note that for a view you'll need unique column names. So you might want to reduce the requirement frm "all values" to the specific columns you're looking for.