[thelist] SQL: looking for resources, have one question

[Chris Blessing]

Chris-

For your query, try this inner join:

SELECT a.manufacturerID, a.ID
  FROM products a, manufacturers b
    WHERE a.manufacturerID = b.id
  ORDER BY a.manufacturerID

This uses the old-style inner join in the WHERE clause.  The current SQL-92
standard for inner joins (preferrably) is used in the FROM clause as such:

SELECT a.manufacturerID, a.ID
  FROM products a
    INNER JOIN manufacturers b
    ON a.manufacturerID = b.id
  ORDER BY a.manufacturerID

I believe the old-style inner join syntax is being/will be deprecated within
the near future.

Both of these use table aliases (a and b) to distinquish within the query
between the two tables.  Once they're joined like this, you can pull info
from either of the tables.  One thing I tend to remember (whether it's
correct or not) is that queries effectively run backwards, the SELECT part
is based on the rest of the query.

As for resources, I don't have a lot bookmarked off-hand but rudy's
question/answer site is excellent for looking up examples.  Naturally I
can't find that link right now. =/  Check these out though:

http://database.ittoolbox.com/di.asp
http://www.databasejournal.com/

Good luck!

Chris Blessing
webguy at mail.rit.edu
http://www.330i.net

> hi.
>
> there are two parts to this post to theList. first part being, frankly
> i'm tired of not understanding SQL and i'd like to get some
> recommendations for a course of action on how i can better learn to use
> it.
>
> 1. i have only used Access so far, and so have not played with any other
> SQL out there. i'd like to be able to work with SQL Server in the
> future. seeing as that i don't have money for that, does MSDE function
> the same as SQL Server, minus some performance restrictions? should i
> install/configure that so i can learn to use SQL Server syntax?
>
> 2. are there any good recommendations for websites and/or books that i
> should take a look at that explain in detail (and possibly with pictures
> [i'm a visual kind of guy]) what the hell is happening when it comes to
> JOINs? (or any other more advanced logic in SQL.)
>
> second part.
>
> i have two tables, like so...
>
> products:
>  * ID
>  * manufacturerID
>
> manufacturers:
>  * ID
>  * name
>
> so far i've this SQL statement...
>
> SELECT manufacturerID, ID
>   FROM products
>     WHERE manufacturerID IN(8,4)
>     ORDER BY manufacturerID
>
> what i want to do is NOT pull back the manufacturerID but rather the
> Name that corresponds to the manufacturerID. the only way i know HOW to
> do this is by using a subSELECT, but i think there is some kind of JOIN
> that will do this. i just don't know how and can't find any good
> resources, or at least any that i can understand.
>
>
> thanks for the help.
> chris.