[thelist] MySQL Table fields

[Howard Cheng]

The original query:

SELECT     i.ItemID, i.CategoryID [whatever your field names are],
            c.CategoryName
FROM       Items i
INNER JOIN Categories c
         ON i.CategoryID = c.CategoryID
WHERE      i.ItemID = '$II'

Notice: We are selecting from only one table and then joining it to the
second table. In your version, you're selecting from both tables and then
joining back to them (which technically is possible, but there's no point
in doing it here).

In your query, you should use the table name exactly as you created it. I
thought you had created them capitalized.

You could also use the alternate form of a join syntax, which is to list
both tables in your FROM clause, but you can't do both. I recommend
sticking to the INNER JOIN method because it's clearer what you're doing.

SELECT     i.ItemID, i.CategoryID [whatever your field names are],
            c.CategoryName
FROM       Items i, Categories c
WHERE      i.ItemID = '$II'
        AND i.CategoryID = c.CategoryID


At 09:01 PM 1/23/2003 +0000, Andrew Maynes wrote:
>I like this error code it reallt confuses things :)
>
>This is now what I have and it works until I put the INNER JOIN in ?
>
>$result=mysql("$DBName","SELECT Items.ItemSKU, Items.ItemName,
>Items.ItemDescription, Items.ItemCost, Items.Category, Items.ShippingCost,
>Items.ItemID, Category.Category FROM Items, Category INNER JOIN category,
>items
>ON Items.Category = Category.Category WHERE ItemID='$II'");
>
>With the inner joins in lower case I get this error:
>
>You have an error in your SQL syntax near ' items ON Items.Category =
>Category.Category WHERE ItemID='2'' at line 1Select an item:
>
>
>When the INNER JOIN is Category, Items I get this error
>
>Not unique table/alias: 'Category'Select an item:
>
>When I take this out:
>INNER JOIN Category, Items ON Items.Category = Category.Category
>
>I dont get any any errors but I am at the beginning again.
>
>Am I doing something reallt stupid or is it all wrong?
>
>I tried the mysql_query and it just kept on showing errors.  All the
>tables are
>in lower case so its strange that the first letter in the table name creates a
>problem when it is lower case in the query.  Is this normal?
>
>Andrew
>
>
>
>
>--
>* * Please support the community that supports you.  * *
>http://evolt.org/help_support_evolt/
>
>For unsubscribe and other options, including the Tip Harvester
>and archives of thelist go to: http://lists.evolt.org
>Workers of the Web, evolt !

::::::::::::::::::::::::::::::::::
Howard Cheng
http://www.howcheng.com/
howcheng at ix dot netcom dot com
AIM: bennyphoebe
ICQ: 47319315