Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    11

    Unanswered: Left Outer Join Problems !

    Hello !
    I want to make two left outer joins.
    The first on table A with table B and the second on table B with table C.
    The problem is that an error occurs and it tells me that left outer join on a field that is part of a left outer join is prohibited.

    This is what i want :
    select a.product , b.description , c.description
    from products as a , media as b , topics as c
    where a.media*=b.media and b.topic*=c.topic

    or

    select a.product , b.description , c.description
    from products as a
    left outer join b on a.media=b.media
    left outer join c on b.topic=c.topic

    any help ?

    thanks !

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Ok this may not be the right way to do it but still :

    select a.product, b.description, c.description
    from products as a, media as b, topics as c,topics as d
    where a.media *=b.media and d.topic *=c.topic


    this syntax was supported in the earlier versions of sql it may not be supported in the future.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    harshal, that can't be right

    you'll get a cross-join effect

    you have no join condition between a-b and c-d
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This give you an error???

    select a.product , b.description , c.description
    from products as a
    left outer join b on a.media=b.media
    left outer join c on b.topic=c.topic

    "left outer join on a field that is part of a left outer join is prohibited"???

    Please post the exact error message you receive when you execute the above statement.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The following works for me:
    Code:
    CREATE TABLE products (
       productId		INT		IDENTITY
    ,  media		VARCHAR(20)	NULL
    ,  product		VARCHAR(20)	NOT NULL
       )
    GO
    
    CREATE TABLE b (
       bId			INT		IDENTITY
    ,  media		VARCHAR(20)	NOT NULL
    ,  [descr]	VARCHAR(20)	NOT NULL
    ,  topic		VARCHAR(20)	NULL
       )
    GO
    
    CREATE TABLE c (
       cId			INT		IDENTITY
    ,  topic		VARCHAR(20)	NOT NULL
    ,  [descr]	VARCHAR(20)	NOT NULL
       )
    GO
    
    INSERT products (media, product) VALUES ('one', 'first')
    INSERT products (media, product) VALUES ('two', 'second')
    INSERT products (media, product) VALUES ('three', 'third')
    
    INSERT b (media, [descr], topic) VALUES ('two', 'zwie', NULL)
    INSERT b (media, [descr], topic) VALUES ('three', 'drei', 'alpha')
    
    INSERT c (topic, [descr]) VALUES ('alpha', 'une')
    GO
    
    select a.product , b.[descr] , c.[descr]
    from products as a
    left outer join b on a.media=b.media
    left outer join c on b.topic=c.topic
    GO
    
    DROP TABLE products
    DROP TABLE b
    DROP TABLE c
    GO
    -PatP

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    man, i just hate it when you guys do that

    you and brett and a coup[le others

    you lay out an entire script like that, with CREATEs and INSERTs and then a beautiful SELECT...

    ... and you never show what the SELECT actually produces

    oh, you always include a whack of DROP TABLE statements at the end

    now that's really helpful, i don't think i could have cleaned up my test database on my own without that

    so basically if i am having trouble getting "the nuance" which your lovely piece of syntax is supposed to demonstrate, i have to mentally picture the rows of your sample tables joined as per your query, to see if maybe i can understand the effect

    or else i have to fire up my database server, fire up my front end gui, fire up my text editor, practice once again my l33t copy/paste sk1lzz0rz, and actually run the query myself

    hard to do if you are not at your home computer, but still wishing to stay current, learn more, understand new syntax, absorb your kindly offered example into the pantheon of one's database knowledge...

    you guys

    really

    Last edited by r937; 05-15-04 at 13:15.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Rudy, Rudy, Rudy...

    You need another Margarita. (Perhaps followed by a Guiness...)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    ... and you never show what the SELECT actually produces
    Having one of those days again, are we Rudy ?!?!

    I'm sorry, you made a good point. My results from that query were:
    Code:
    (1 row(s) affected)
    
    
    (1 row(s) affected)
    
    
    (1 row(s) affected)
    
    
    (1 row(s) affected)
    
    
    (1 row(s) affected)
    
    
    (1 row(s) affected)
    
    product              descr                descr                
    -------------------- -------------------- -------------------- 
    first                NULL                 NULL
    second               zwie                 NULL
    third                drei                 une
    
    (3 row(s) affected)
    -PatP

Posting Permissions

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