Joins ?

Hello Ladies & Gents

I am rather new to SQL and I was wondering if someone would be so kind to provide how-to information on creating Joins and the difference between Inner & Outer Joins and/or where I can find information on how-to...

I have a SQL Database with 3 Tables

tblUser field(s) Last_Name, First_Name
tblRights field(s) Supervisor, Manager, Consultant, Date_Modified
tblDept field(s) Accounting, IS, Training, Mgmt

I need the Last name & First name from tblUser in corralation with the Users rights from tblRights and the corralating Department from tblDept.
Also I need to be able to tell the last time the Users rights were updated/modified and by whom. I need to have the output piped into a file.

Any help and/or suggestions of how to do this would be greatly appreciated. Thanks so much for all of your help in advanceTehe

Good Luck Pharoah - U gotta a long way 2 go

Maybe heres a pointer

Understand Normalization

Learning 2 make joins will make more sense.

ie. How many rights do you want users to have (pun here)

Is it a 1 to Many ?

Sounds 2 me like U may need 2 add some Id's to these Rows

User_id
Dept_id
etc

Probably splitting em out into instancing tables -
the rights (we won't talk about right outer joins) one at least -

Here's Some Skeleton Code to get you started

CREATE TABLE tblUser (UserId int,FirstName VarChar(50),LastName VarChar(50))
CREATE TABLE tblRights (RightsId Smallint,RightsDesc VarChar(50))
CREATE TABLE tblDept (DeptId SmallInt,DeptDesc VarChar(50))
CREATE TABLE tblUserRights (UserId SmallInt,RightsId SmallInt)

INSERT INTO tblUser (UserId,FirstName,LastName) VALUES (1,'Fred','FlintStone')
INSERT INTO tblRights (RightsId,RightsDesc) VALUES (1,'BossMan')
INSERT INTO tblUserRights (UserId,RightsId) VALUES (1,1)

SELECT tblUser.UserId,tblRights.RightsDesc
FROM tblUser,tblRights,tblUserRights
WHERE
tblUser.UserId = tblUserRights.UserId
AND
tblUserRights.RightsId = tblRights.RightsId

then DeNormalize it back to the begining - lol

Once you got yer Data - pop it out using EM wizard / DTS or if U feeling adventurous with TSQL.

Hope I Helped n Did'nt scare U off

GW

join several fields to create new field

What would be the recommendation/approach in creating a seperate field in which joins several differate fields together.

I have a table with field name a, b, and c. I want the information in those fields to be populated in a seperate field, d.

So instead of:

a

122

b

joe

c

st

I would have:

d

122 joe st

Thanks!

You need to separate the storage of data from it's presentation. Generally you would store data at the lower level, then combine it when you retrieve it. You can do that either in the application that retrieves it, or in SQL. For instance in your example:

Select a + ' ' + b + ' ' + c as d

would give the result you are after

|||Could this be accomplished in a sql view?|||It could, but in my view you're still using the database to process data rather than storing it. You could also use a stored procedure|||I should clarify myself. I want the attribute data in fields

a,b,c to popluate a new field d. I've created field d in the

table with fields a,b,c. Now I just need to populate it.

Thanks.|||Possibley a better way of doing this is in the view because I

don't need to store the actual data. I just need a way to present

it in an application. So if there's a sql statement that I could

add to my existing view, that would work as well.

One caveaet in the current view is that I still want the a,b,c fields to present themselves in the view.

So ...

select a, b, c, d (as the virtual field which concatenates a,b,c)

from f

the presentation would be:

a b c d

123 joe st 123 joe st|||

The field 'd' is totally superfluous. There is no need to duplicate the data in storage.

Select a, b, c, a + ' ' + b + ' ' + c as d

Will produce exactly the output you want. You can put this select either in a stored proc, a view or in raw sql, it doesn't matter.

|||This works ... sort of.

If there are any null fields, it returns nothing. So is there a way around that.

Something can be built into the view that handles: if null then '' otherwise d + ' ' ?|||I've worked with the view and it is working well enough to return valid

results. I can't use it for the application however. I need

to actually store the data in a seperate field.

If there are thoughts on how to make this happen, I would appreciate it.

The problems I see are:

the new field must have spaces - example 122 joe st

there may be a direction but not in all cases - example 122 N joe st

Thanks.|||Depending on how complex you want your logic to be, you could either use Computed Columns (for simple logic. See http://msdn2.microsoft.com/en-us/library/ms191250.aspx) or DML triggers (for more complex logic. See http://msdn2.microsoft.com/en-us/library/ms191524.aspx).

join several fields to create new field

What would be the recommendation/approach in creating a seperate field in which joins several differate fields together.

I have a table with field name a, b, and c. I want the information in those fields to be populated in a seperate field, d.

So instead of:

a

122

b

joe

c

st

I would have:

d

122 joe st

Thanks!

You need to separate the storage of data from it's presentation. Generally you would store data at the lower level, then combine it when you retrieve it. You can do that either in the application that retrieves it, or in SQL. For instance in your example:

Select a + ' ' + b + ' ' + c as d

would give the result you are after

|||Could this be accomplished in a sql view?|||It could, but in my view you're still using the database to process data rather than storing it. You could also use a stored procedure|||I should clarify myself. I want the attribute data in fields

a,b,c to popluate a new field d. I've created field d in the

table with fields a,b,c. Now I just need to populate it.

Thanks.|||Possibley a better way of doing this is in the view because I

don't need to store the actual data. I just need a way to present

it in an application. So if there's a sql statement that I could

add to my existing view, that would work as well.

One caveaet in the current view is that I still want the a,b,c fields to present themselves in the view.

So ...

select a, b, c, d (as the virtual field which concatenates a,b,c)

from f

the presentation would be:

a b c d

123 joe st 123 joe st|||

The field 'd' is totally superfluous. There is no need to duplicate the data in storage.

Select a, b, c, a + ' ' + b + ' ' + c as d

Will produce exactly the output you want. You can put this select either in a stored proc, a view or in raw sql, it doesn't matter.

|||This works ... sort of.

If there are any null fields, it returns nothing. So is there a way around that.

Something can be built into the view that handles: if null then '' otherwise d + ' ' ?|||I've worked with the view and it is working well enough to return valid

results. I can't use it for the application however. I need

to actually store the data in a seperate field.

If there are thoughts on how to make this happen, I would appreciate it.

The problems I see are:

the new field must have spaces - example 122 joe st

there may be a direction but not in all cases - example 122 N joe st

Thanks.|||Depending on how complex you want your logic to be, you could either use Computed Columns (for simple logic. See http://msdn2.microsoft.com/en-us/library/ms191250.aspx) or DML triggers (for more complex logic. See http://msdn2.microsoft.com/en-us/library/ms191524.aspx).