Table Joins

PDavis,

Joins can be done in different ways (and in different ways on different database servers) so it can get a little tricky.

Lets say you had a database called People and a table called Person with the following columns:

Person_ID
Person_FirstName

and you had a table called Books with the following columns:

Book_PersonID
Book_Name

You could use a SQL statement like:

USE People
SELECT p.Person_ID, p.Person_FirstName, b.Person_ID, b.Book_Name
FROM Person AS p LEFT OUTER JOIN Books AS b
ON p.Person_ID = b.Person_ID

Or you could write a SQL statement like:

USE People
SELECT p.Person_ID, p.Person_FirstName, b.Person_ID, b.Book_Name
FROM Person AS p, Books AS b
WHERE p.Person_ID *= b.Person_ID

Both those queries would return the same recordset.

Peter-
is that Access SQL syntax? or perhaps Perl?
I've never seen the USE people before. exept when at the command line for a mySQL DB.

I work primarily with SQL Server 7 and ASP, and we specify the DB in the connection string. All at once.

PHP (with mySQL) is similar to ASP in that you specify the DB before you ever execute a query. But with PHP, you first connect to the server, then select the DB.

Max,

That syntax was for SQL Server. You're right, you don't have to specify the database with the USE statement, but you can, I was just trying to be thorough with that example.

can you connect to one DB in the connection string (like in ASP), then put "USE db_name" in the query and actually change DBs?
if so, would this change the connection for everything, or just that one query?

this is interesting...

Max,

The SQL query still has to correspond with the connection object. So if your connection object uses a database called "TEST" and then you try a statement with "USE NOT_TEST" it will fail.

thanks!