Results 1 to 2 of 2

Thread: Joins ??

  1. #1
    Join Date
    Jun 2003

    Question Unanswered: 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 advance

  2. #2
    Join Date
    Jan 2003
    Nottinghamshire, UK

    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


    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
    tblUser.UserId = tblUserRights.UserId
    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

    Last edited by GWilliy; 06-11-03 at 12:33.
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts