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 
Free Windows Admin Tool Kit Click here and download it now
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
Free Windows Admin Tool Kit Click here and download it now
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
Free Windows Admin Tool Kit Click here and download it now
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

Free Windows Admin Tool Kit Click here and download it now
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

Free Windows Admin Tool Kit Click here and download it now
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
Free Windows Admin Tool Kit Click here and download it now
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
Free Windows Admin Tool Kit Click here and download it now
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

Free Windows Admin Tool Kit Click here and download it now
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!

Free Windows Admin Tool Kit Click here and download it now
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?

Free Windows Admin Tool Kit Click here and download it now
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. You need to specify the schema explicitly when creating a table. I did a quick Google search on this topic and this is what came up:

http://stackoverflow.com/questions/1489872/how-do-i-create-a-sql-table-under-a-different-schema

Free Windows Admin Tool Kit Click here and download it now
September 12th, 2015 10:23pm

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:

Free Windows Admin Tool Kit Click here and download it now
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

Free Windows Admin Tool Kit Click here and download it now
September 13th, 2015 9:27pm

As I said, run query. Copy result to query window and execute it.

September 14th, 2015 2:44am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics