SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2004
    Location
    Meriden, CT
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query help - outer join

    Can someone help me with this query...?


    declare @proj int, @week int
    set @proj = 5
    set @week = 3

    SELECT b.trans_id, a.emp_id, a.fname, a.lname, b.role_id, a.fname + ' ' + a.lname fullname, 1 status, e.descr currenttask
    FROM resources a, weekly_proj_res b, project d, role e
    LEFT OUTER JOIN e ON b.role_id = e.role_id
    WHERE (@week = b.week_id) AND (a.emp_id = b.emp_id) AND (@proj = b.proj_id) AND (d.proj_id = b.proj_id)

    the error I'm getting is:
    Server: Msg 1012, Level 15, State 1, Line 6
    The correlation name 'e' has the same exposed name as table 'e'.



    If I take the "LEFT OUTER JOIN e ON b.role_id = e.role_id" line out, the query will run, but I get a Cartesian join. I want the outer join because I still want records from b which don't refer to a recored from e.



    Thanks,

    -Dave

  2. #2
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT
      	b.trans_id
      ,	a.emp_id
      ,	a.fname
      ,	a.lname
      ,	b.role_id
      ,	a.fname + ' ' + a.lname fullname
      ,	1 status
      ,	e.descr currenttask
      FROM
      	resources a
      INNER JOIN
      	weekly-proj_res b ON a.emp_id = b.emp_id
      INNER JOIN
      	project d ON b.proj_id = d.proj_id
      LEFT OUTER JOIN
      	Role e ON b.role_id = e.role_id
      WHERE
      	b.week_id = @week AND
      	b.proj_id = @proj
    Basically, you had the table 'role' - with the alias of 'e' - in the from clause, AND you were adding it again when you did your outer join. I've rewritten the query to use joins everywhere (you were effectively doing inner joins in the where clause).

    To show you what you did wrong i've also rewritten the query like this:
    Code:
    SELECT
     	b.trans_id
     ,	a.emp_id
     ,	a.fname
     ,	a.lname
     ,	b.role_id
     ,	a.fname + ' ' + a.lname fullname
     ,	1 status
     ,	e.descr currenttask
     FROM
     	resources a
     ,	weekly_proj_res b
     ,	project d
     LEFT OUTER JOIN
     	role e ON b.role_id = e.role_id
     WHERE
     	(@week = b.week_id) AND
     	(a.emp_id = b.emp_id)AND
     	(@proj = b.proj_id) AND
     	(d.proj_id = b.proj_id)
    I've removed the table 'role' from the FROM clause, and added the table name in the join.

    Hopefully these should work.

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2004
    Location
    Meriden, CT
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    Thanks for the help, that worked well. I am just a little confused on the whole concept though. So tell me if I'm getting this right... Anytime you have an inner join, you can just use the where clause to state that and it means the same thing...? When you want to do an outer join, you need to use a LEFT/RIGHT OUTER JOIN and the table you are using needs to be first "used" in the join and not in the from clause...? Is there an easy way to understand the difference between a left and right outer join?

    I appreciate the help, thanks!

    -dAVE

  4. #4
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In regards to your first question, you've got it backwards Whenever you're joining tables in the where clause you should change the query to use the JOIN syntax.

    This is better because it keeps all your table joins in one place, and allows easy switching between INNER JOIN and LEFT JOIN if something changes, and more!

    Here's an explanation of JOINS and their differences

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,332
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tradnbillies
    Is there an easy way to understand the difference between a left and right outer join?
    yes, there is

    in a LEFT OUTER JOIN, all the rows from the left table are returned (plus matching rows from the right table, if any)

    in a RIGHT OUTER JOIN, all the rows from the right table are returned (plus matching rows from the left table, if any)

    so, to summarize --

    LEFT OUTER JOIN --> all left table rows
    RIGHT OUTER JOIN --> all right table rows

    this has such an elegant symmetry, don't you think?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Nov 2004
    Location
    Meriden, CT
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I appreciate the help...
    I also appreciate the sarcasm.

    What I dont quite understand is the difference between:


    LEFT OUTER JOIN tableB b ON a.id = b.id
    and
    RIGHT OUTER JOIN tableA ON b.id = a.id

    is it that it depends on which table you are "select"ing the data from? The table in the FROM table would be the left table, and the right table would be the other table...?

    Again, appreciate the help... it's just a little fuzzy for me. I dont like the "w3cschools" explanation of the joins, but thanks for the link... I had checked that out before.

    -Dave

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,332
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, quick quiz:

    what is the difference between this --

    ... from foo left outer join bar on foo.x = bar.y

    and this --

    ... from bar right outer join foo on bar.y = foo.x

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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