Select from multiple tables

This sounds like a simple join, could you give a more concrete example?

Jon

On Fri, 6 Jun 2003, Jon Earle wrote:

On Fri, Jun 06, 2003 at 11:17:01 -0400,
Jon Earle <je_pgsql@kronos.honk.org> wrote:

Hi,

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?

It sounds like you want to join the tables. It is hard to say for sure
since you haven't provided details about the tables and what you are
trying to get back.

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/

On Friday 06 Jun 2003 4:17 pm, Jon Earle wrote:

Hi,

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?

A join?

SELECT * FROM tbl_a,tbl_b WHERE tbl_b.some_field=tbl_a.other_field;

Or have I got the wrong end of the stick here?
--
Richard Huxton

On Fri, 6 Jun 2003, Jon Earle wrote:

Hi,

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?

You probably want a join or a sub select.

select * from table1 t1 left join table2 t2 on (t1.id=t2.t1id);

select * from table1 where id in (select id from table2 where compid=3);

Or is it more complex than that?

On Fri, Jun 06, 2003 at 11:17:01AM -0400, Jon Earle wrote:

Hi,

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?

SELECT * FROM table1 t1, table2 t2 WHERE t1.key_field = t2.key_field;
--
Jim C. Nasby (aka Decibel!) jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Fri, 6 Jun 2003, scott.marlowe wrote:

You probably want a join or a sub select.

Or is it more complex than that?

Thank you to everyone who responded to my (very simple) question. Yes, it
was a subselect type op I wanted to do, but I didn't know that that was
what it was called.

I ended up with a query such as:

select table1.id2, fld2, table2.fld3
from table1, table2
where id = '1'
and table1.id2 = table2.id2
and fld2 > 0;

Cheers!
Jon

--
Jon Earle

SAVE FARSCAPE http://www.savefarscape.com/