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

Joins

Hi,

What types of Joins MS SQL has? and pls provide definition of each Joins.

and with realtime example pls!! which you used in development

Thanks
Seshu

This is a pretty good resource, with examples:

Using Joins
http://msdn2.microsoft.com/en-us/library/ms191472.aspx

It is broken down into several articles covering how to join using inner, outer, cross joins, etc.