SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    problem with a JOIN query

    Can't figure this out. Sytactically it all looks good, but I'm getting this error:
    SQL-query :

    SELECT * FROM ltw_articles, ltw_authors
    LEFT JOIN ltw_articles ON (ltw_articles.authorID = ltw_authors.authorref) LIMIT 0, 30

    MySQL said:

    Not unique table/alias: 'ltw_articles'
    This is the query. Again it looks good to me.
    Code:
    SELECT * FROM ltw_articles, ltw_authors
    	LEFT JOIN ltw_articles ON (ltw_articles.authorID = ltw_authors.authorref)
    I need to narrow this query down with a WHERE but I'd be happy to get this JOIN working.

    Thanks
    Aaron
    Aaron Brazell
    Technosailor



  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why do you have ltw_articles twice?

    Should be:
    Code:
    SELECT * 
      FROM ltw_articles
      LEFT JOIN ltw_authors ON authorref = authorID
    Are you sure you want a LEFT JOIN?
    Last edited by MattR; Jul 18, 2002 at 07:04.

  3. #3
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by MattR
    Code:
    SELECT * 
      FROM ltw_articles
      LEFT JOIN ltw_authors ON authorref = authorID
    Are you sure you want a LEFT JOIN?
    THERE we go. Actually, more specifically:
    Code:
    SELECT * 
      FROM ltw_articles
      LEFT JOIN ltw_authors ON ltw_authors.authorref = ltw_artices.authorID
    Yeah, the rersults are what I was looking for. I did need a LEFT JOIN but I'd be open to critique if there's a bettrer way to accomplish the same thing in MySQL (thinking after recent db questions here I may need to switch to postgresql)

    SKetch
    Aaron Brazell
    Technosailor



  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,269
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    sketch, the best way to "accomplish" what a LEFT JOIN does, is with the LEFT JOIN syntax

    in that regard, mysql is just fine

    LEFT JOIN is a relatively recent addition to sql -- at least, it feels that way if you've ever worked with a database that took its time adopting standard sql

    the only other way to get the same results, and the way we used to do it before LEFT JOIN came along, was
    Code:
    select * 
      from ltw_articles
         , ltw_authors 
     where ltw_artices.authorID = ltw_authors.authorref
    union all
    select * , null, null, null...
      from ltw_articles
     where not exists
           ( select 1
               from ltw_authors
              where authorref = ltw_authors.authorref )
    note you have to "hard code" into the select list of the second subquery as many nulls as there are columns of the authors table
    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
  •