Thread: Select from multiple tables
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? Cheers! Jon -- Jon Earle SAVE FARSCAPE http://www.savefarscape.com/
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/
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, 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 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
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/
This sounds like a simple join, could you give a more concrete example? Jon 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? > > Cheers! > Jon > > -- > Jon Earle > > SAVE FARSCAPE http://www.savefarscape.com/ > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
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.