[thelist] sql statement
Ken Schaefer
Ken at adOpenStatic.com
Tue Mar 29 16:56:42 CST 2005
INNER JOIN requires a match on both sides.
OUTER JOIN does not require a match, and where there is no match, a NULL will
be substituted. Depending on whether you use a LEFT OUTER JOIN, RIGHT OURTER
JOIN, or FULL OUTER JOIN, you will get NULLs on the right, left, or both
sides.
-- always require a match in table1 with table2
INNER JOIN
Table2
ON
Table1.ID = Table2.ID
-- if no match in table2, then select the fields from
-- table1, and put in NULLs for the table2 fields
LEFT OUTER JOIN
Table2
ON
Table1.ID = Table2.ID
-- if no match in table1, then select the fields from
-- table2, and put in NULLs for the table1 fields
RIGHT OUTER JOIN
Table2
ON
Table1.ID = Table2.ID
-- select all records, and if no match
-- substitute a NULL on either the left or right
FULL OUTER JOIN
Table2
ON
Table1.ID = Table2.ID
Cheers
Ken
--
www.adOpenStatic.com/cs/blogs/ken/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Donnie Blaker
: Subject: Re: [thelist] sql statement
:
: if you dont mind me asking, whats the difference between an inner and an
: outer
:
: >From: Ed McCarroll <Ed at ComSimplicity.com>
: >Reply-To: "thelist at lists.evolt.org" <thelist at lists.evolt.org>
: >To: "thelist at lists.evolt.org" <thelist at lists.evolt.org>
: >Subject: Re: [thelist] sql statement
: >Date: Tue, 29 Mar 2005 13:52:24 -0800
: >
: >To list all size names for product 213:
: >
: > SELECT size.name
: > FROM size S
: > INNER JOIN prod_size PS
: > ON S.sid = PS.sid
: > WHERE PS.prodid = 213
: >
: >or equivalent...
: >
: >--
: >Ed McCarroll (310) 838-4330
: >Ed at ComSimplicity.com http://www.ComSimplicity.com
: >
: >
: >Quoting Donnie Blaker <filterhead80 at hotmail.com>:
: >
: > > I have two tables. sizes and prod_size.
: > >
: > > size has sid, name, abrv
: > > prod_size has sid, prodid
: > >
: > > when I do this look up I would like in one query get the all the
: > > sizes for
: > > that product. normally I would do two queries but I dont think
: > > that is the
: > > correct way.
: > >
: > > does someone know what I would use? like left join or join or
: > > something
: > > like that
: > >
: > > thanks
More information about the thelist
mailing list