Joining 3 or more Multi-tables Using SQL Views
Hi,
I am new to programming trying hard to learn. I made a code for Views in mssql for the 2 tables below:
I am wondering if I could make it 3, 4 or more tables at the same time. How can incorporate in the following codes I made.
Create View vwEmployeeInfoByGender
as
Select FirstName, MiddleName, FamilyName, Gender, BirthDate,
MaritalStatus
from tblEmployeeInfo
join tblGender
on tblEmployeeInfo.GenderID
= tblGender.GenderID
Any help please...
Thank you very much.
Cioden_sky
September 8th, 2015 10:29am
Hello,
Just add some more JOIN clauses to the view, like
Create View vwEmployeeInfoByGender
as
Select FirstName, MiddleName, FamilyName, Gender, BirthDate, MaritalStatus
from tblEmployeeInfo
join tblGender
on tblEmployeeInfo.GenderID = tblGender.GenderID
join tblMartialStatus
on tblEmployeeInfo.MartialStatus = tblMartialStatus.MartialStatus
September 8th, 2015 10:35am
A very fast answer is that you can join as many objects(tables,views) as you want. But before using a join take a look at the
fundamentals. It is easy
to understand how it works and then create the correct join to resolve your problem.
-
Marked as answer by
Cioden_sky
Tuesday, September 08, 2015 11:20 AM
September 8th, 2015 10:38am
A very fast answer is that you can join as many objects(tables,views) as you want. But before using a join take a look at the
fundamentals. It is easy
to understand how it works and then create the correct join to resolve your problem.
-
Marked as answer by
Cioden_sky
Tuesday, September 08, 2015 11:20 AM
September 8th, 2015 10:38am
A very fast answer is that you can join as many objects(tables,views) as you want. But before using a join take a look at the
fundamentals. It is easy
to understand how it works and then create the correct join to resolve your problem.
-
Marked as answer by
Cioden_sky
Tuesday, September 08, 2015 11:20 AM
September 8th, 2015 10:38am
A very fast answer is that you can join as many objects(tables,views) as you want. But before using a join take a look at the
fundamentals. It is easy
to understand how it works and then create the correct join to resolve your problem.
-
Marked as answer by
Cioden_sky
Tuesday, September 08, 2015 11:20 AM
September 8th, 2015 10:38am
Hi Olaf!
Thank you so much! Got it! This is exactly what I am looking for.
Cheers!
Cioden_sky
September 8th, 2015 11:18am
Hi Emmanouil!
Great reference! I book-marked it and you are right need to read more about it!
Thanks for sharing,
Cioden_sky
September 8th, 2015 11:20am
Making tbl prefix for tables is a common newbies mistake. It's a bad habit, don't use tbl prefix for tables. In the query it is a good idea to specify schema name in the from clause.
September 8th, 2015 12:30pm
Hi Naomi,
If you don't mind, can you give example of the schema name in the form. Really this things are new to me..
thanks,
Cioden_sky
September 8th, 2015 1:05pm
You can read about Schema
here. There are a lot of uses for the schema. For example you can have many schema (but never a schema under another schema),
you can have the same table names under different schema etc. Take a look at the link!
-
Marked as answer by
Cioden_sky
Tuesday, September 08, 2015 2:21 PM
September 8th, 2015 1:15pm
You can read about Schema
here. There are a lot of uses for the schema. For example you can have many schema (but never a schema under another schema),
you can have the same table names under different schema etc. Take a look at the link!
-
Marked as answer by
Cioden_sky
Tuesday, September 08, 2015 2:21 PM
September 8th, 2015 1:15pm
You can read about Schema
here. There are a lot of uses for the schema. For example you can have many schema (but never a schema under another schema),
you can have the same table names under different schema etc. Take a look at the link!
-
Marked as answer by
Cioden_sky
Tuesday, September 08, 2015 2:21 PM
September 8th, 2015 1:15pm
You can read about Schema
here. There are a lot of uses for the schema. For example you can have many schema (but never a schema under another schema),
you can have the same table names under different schema etc. Take a look at the link!
-
Marked as answer by
Cioden_sky
Tuesday, September 08, 2015 2:21 PM
September 8th, 2015 1:15pm
Create View vwEmployeeInfoByGender
as
Select e.FirstName, e.MiddleName, e.FamilyName, g.Gender, e.BirthDate, m.MaritalStatus
from dbo.tblEmployeeInfo e
join dbo.tblGender g
on e.GenderID = g.GenderID
join dbo.tblMartialStatus ms
on e.MartialStatus = ms.MartialStatus
Note, that I added dbo and aliases for all tables involved. If the view involves more than 1 table, you need to give correct alias to all columns in the select clause even if they come from a particular table. It will be much easier to maintain properly written
query.
September 8th, 2015 2:18pm
Hi Naomi / Emmanouil,
I've been looking for this schema actually sometime while I was doing tables as I was wondering if there is a way that I could group my tables because its becoming confusing as more tables you are creating.
Thank you so much for this. You are so BIG help to us beginners in programming!
Keep up the good work!!
Cheers!
Cioden_sky
September 8th, 2015 2:37pm
Yes, you may want to create a new schema to group related tables into schema. Say, this is how AdventureWorks (the sample database) is set up.
September 8th, 2015 2:43pm
What a great suggestion Naomi!
All of this are new to me.. and it looks clean right now. I changed it!
September 9th, 2015 6:42pm
Hi Naomi,
Just a follow-up question.
I've done already the schemas for all the tables a you suggested.
But when I created a new table the dbo prefix (dbo.ADM.NewTable) appears.
I'd like this dbo not to automatically appear whenever i created a new table as it requires me to alter whenever I created a new table.
Is this something that is required to determine the creator of the table?
Otherwise, I'd like that to remove.
Further help would be most appreciated...
Many thanks,
Cioden_sky
September 11th, 2015 7:47pm
Actually you should leave it there because it is "helpful" for performance. Take a look at
this
article so as to understand better what dbo is.
If you really want to remove it there are ways like do it by code :
IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'MyDbo'))
BEGIN
EXEC ('CREATE SCHEMA [MyDbo] AUTHORIZATION [dbo]')
END
ALTER SCHEMA MyDbo
TRANSFER dbo.your_old_table
GO
or
1 .Click F4 or ( view > Properties window) , 2 .then click on your Table. 3 .in properties window , in schema field , change the attribute.
Source : How to remove or alter the dbo prefix from SQL Server 2012 tables?
September 12th, 2015 7:45am
Actually you should leave it there because it is "helpful" for performance. Take a look at
this
article so as to understand better what dbo is.
If you really want to remove it there are ways like do it by code :
IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'MyDbo'))
BEGIN
EXEC ('CREATE SCHEMA [MyDbo] AUTHORIZATION [dbo]')
END
ALTER SCHEMA MyDbo
TRANSFER dbo.your_old_table
GO
or
1 .Click F4 or ( view > Properties window) , 2 .then click on your Table. 3 .in properties window , in schema field , change the attribute.
Source : How to remove or alter the dbo prefix from SQL Server 2012 tables?
-
Marked as answer by
Cioden_sky
18 hours 26 minutes ago
September 12th, 2015 11:44am
By default the new tables are created in the dbo schema.
This is not entirely correct. Tables are created in the default schema of the current user, as demonstrated by this snippet:
CREATE USER hutte WITHOUT LOGIN WITH DEFAULT_SCHEMA = guest
GRANT CONTROL TO hutte
go
EXECUTE AS USER = 'hutte'
go
CREATE TABLE huttestable(a int NOT NULL)
go
REVERT
GO
SELECT * FROM guest.huttestable
go
DROP TABLE guest.huttestable
DROP USER hutte
However, the default schema for a newly created user is dbo.
To change the default schema for a current user, you can do:
ALTER USER usr WITH DEFAULT_SCHEMA = someschema
September 13th, 2015 5:23am
Hello Naomi,
I now agree to keep the dbo schema.
How can I get back the dbo to all the schema i changed see attached screenshot:
September 13th, 2015 8:38am
SELECT 'ALTER SCHEMA dbo TRANSFER ' +
quotename(s.name) + '.' + quotename(o.name)
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.is_ms_shipped = 0
AND o.schema_id <> 1
Copy result and execute it.
September 13th, 2015 9:58am
Hi Erland,
Thanks for great suggestion!
If you don't mind, shall I run these as row or should I inject some more code in here?
If you notice, I attached the screenshot of my tables and want to put dbo's to those tables with ADM and HRM.
Thanks,
Cioden_sky
September 13th, 2015 9:27pm
As I said, run query. Copy result to query window and execute it.
September 14th, 2015 2:44am