Re: Select from multiple tables

At 17:17 06.06.2003, Jon Earle said:
--------------------[snip]--------------------
>I want to select data from two tables, with the keying information for the
>second table coming from the select results of the first.  Can this be
>done in one call, or will I need to resort to two calls - one to get the
>record from the first table, then a second call to get the record from the
>second table based on a key contained in the first results set?
--------------------[snip]--------------------

Hint - get yourself a good book on SQL, or consult some online manuals.
What you want to do is called a JOIN:

SELECT table1.*, table2.* FROM table1
JOIN table2 ON table2.key = table1.foreignkey
WHERE table1.somcol = somevalue

This will give you all rows from table1 where a matching row in table2 exists.

SELECT table1.*, table2.* FROM table1
LEFT OUTER JOIN table2 ON table2.key = table1.foreignkey
WHERE table1.somcol = somevalue

This will give you all rows from table1 whether a matching row in table2
exists or not.

SELECT table1.*, table2.* FROM table1
RIGHT OUTER JOIN table2 ON table2.key = table1.foreignkey
WHERE table1.somcol = somevalue

This will give you all rows from table2 whether a matching row in table1
exists or not.




--
   >O     Ernest E. Vogelsinger
   (\)    ICQ #13394035
    ^     http://www.vogelsinger.at/