SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Ceci n'est pas Zoef Zoef's Avatar
    Join Date
    Nov 2002
    Location
    Malta
    Posts
    1,111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Syntax for doing a left join on an alias table?

    This is the situation. I'm (slowly, step by step) building a query with the following FROM clause:
    Code:
    FROM configoption LEFT JOIN item ON configoption.optionid = item.id, config
    And it works beautifully!

    The thing is, I really need the 'item' table to be an alias, so I try this:
    Code:
    FROM configoption LEFT JOIN item AS option ON configoption.optionid = option.id, config
    And this is the resulting error:
    MySQL error nr 1064: You have an error in your SQL syntax near 'option ON configoption.optionid = option.id, config .....

    I've been banging my head on this for the last couple of hours, and yes that included reading the manual and searching the web . If anyone could help I'd much apreciate it.

    Rep points to be won!!!

    Rik
    English tea - Italian coffee - Maltese wine - Belgian beer - French Cognac

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try it without the AS

    try it with another alias (option is too easily a reserved word)

    what's the entire query? what's that ", config" after the ON condition?

    rudy
    http://r937.com/

  3. #3
    Ceci n'est pas Zoef Zoef's Avatar
    Join Date
    Nov 2002
    Location
    Malta
    Posts
    1,111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    try it with another alias (option is too easily a reserved word)
    Yes indeed! 'option is in the reserved words list! Hadn't thought of that, seems obvious now .

    Thanks a lot, Rudy! Rep on it's way!

    btw, 'config' is another table.

    Well, I'll continue constructing the rest of the query now . I've got to add another 2 tables to the mix. Just in case you're curious... This is part of a 'configure your PC' thingy I'm making for the website of a local computer shop.

    Thanks again.

    Rik
    English tea - Italian coffee - Maltese wine - Belgian beer - French Cognac

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ok, i get it, config as another table after the comma

    i prefer to use JOIN syntax for all joins

    lot easier that way

  5. #5
    Ceci n'est pas Zoef Zoef's Avatar
    Join Date
    Nov 2002
    Location
    Malta
    Posts
    1,111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    ok, i get it, config as another table after the comma

    i prefer to use JOIN syntax for all joins

    lot easier that way
    If you say so.

    I would like to use the join syntax but it confuses me, especially with multiple joins. To take the same query, this is how I made it work with the conditions in the where clause:
    Code:
    FROM
    	configoption LEFT JOIN item AS options ON configoption.optionid = options.id,
    	config
    WHERE
    	configoption.configid = $configid
    	AND configoption.configid = config.id
    	AND config.cookieid = '" . getUserCookie() . "'
    So then I tried this:
    Code:
    FROM
    	configoption LEFT JOIN item AS options ON configoption.optionid = options.id,
    	configoption JOIN config ON configoption.configid = config.id
    WHERE
    	configoption.configid = $configid
    	AND config.cookieid = '" . getUserCookie() . "'
    But it gives me Not unique table/alias: 'configoption'. I guess I should sorta combine both joins or whatever but I can't put my finger on it... And what do you do when youu want to combine even more tables???

    I can see it's a much better and clearer way of doing things (keep the joins seperated from the filters) but I can't wrap my head around it.

    Rik
    English tea - Italian coffee - Maltese wine - Belgian beer - French Cognac

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
      from configoption
    left
      join item as options 
        on configoption.optionid = options.id
    left
      join config 
        on configoption.configid = config.id
       and       config.cookieid = '" . getUserCookie() . "'
    
     where configoption.configid = $configid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Ceci n'est pas Zoef Zoef's Avatar
    Join Date
    Nov 2002
    Location
    Malta
    Posts
    1,111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, that worked! And I even thought I understood how... But then I started adding more tables and I ran into trouble again. So I reverted back to putting the conditions in the 'where'.

    I really fail to see how using 'join' is easier.

    I can't get my head around how these 'join' things 'flow', if you know what I mean. Especially when it gets a little more complicated. Must try to learn this at some point in the future.

    Any books/tutorials you would recommend?

    Rik
    English tea - Italian coffee - Maltese wine - Belgian beer - French Cognac


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
  •