Re: Query with keys and maybe a cursor !

In article <gZL_5.4172$O%.675099_at_newsc.telia.net>,   "PelleSvanslös" <jamppi_at_telia.com> wrote:
> hi there !!!
>
> yes the qyery will do it !!!
> but i need it to take out the description for every "Productid" and
> "Producttype"
> so ill probably need to create a cursor or something likely ??
>
> now the query takes out just the one description ! i need them all!!
>
> but i dont know how to do it ??
>
> /Jarmo
>
> <steveee_ca_at_my-deja.com> skrev i meddelandet
> news:91aont$rvs$1_at_nnrp1.deja.com...
> > In article <91anur$r5j$1_at_nnrp1.deja.com>,
> > Mark D Powell <markp7832_at_my-deja.com> wrote:
> > > In article <Pine.SOL.4.10.10012141535240.18170-
> > > 100000_at_kairos.algonet.se>,
> > > Jarmo Harju <jamppi_at_algonet.se> wrote:
> > > > Hi !!
> > > >
> > > > i have 2 tabels
> > > > the first one with lots of product
> > > > and the next one with a load of descriptions of the products
> > > > and the only way to receive the descriptions is by comming up
 with a
> > > > key combined of 2 post in the products tabel.
> > > >
> > > > this is the query im using when taking out single posts
> > > >
> > > > SELECT description FROM products WHERE
> > > > Productid ='something' and producttype ='something'
> > > > and YearModelIn =< '1191 and ModelOutYear >= '1998';
> > > >
> > > > my question is ! how do i get this one to go thru all records
> > > > in the database, i wnt all the desciptions for every product
> > > > slammed to gether to a new tabell ??
> > > > i guess i need to create som kind of Cursor and loop thru all
 the
 post ?
> > > >
> > > > but how do i do it ??
> > > >
> > > > /JArmo
> > > >
> > > I am a little bit confused by the wording of your post but what I
 think
> > > you want is to perform a join. Whenever you need data from more
 than
> > > one table in one result set you write a query which joins the
 tables
> > > together using columns from each table that hold common values.
 In
 the
> > > absence of your table descriptions here is a generalized example:
> > >
> > > select A.product_id, A.col2, B.description
> > > from product A
> > > ,descriptsion B
> > > where A.product_id = B.product_id ;
> > >
> > > Anyway, I hope this is one some help to you.
> > >
> > > --
> > > Mark D. Powell -- The only advice that counts is the advice that
> > > you follow so follow your own advice --
> > >
> > > Hi,
> > Mark's answer will do the trick..quick note tho'..and it's probably
 just
> > a typo in your WHERE clause...add a second single quote
 after '1191..
> >
> > <pasted>
> > and YearModelIn =< '1191 and ModelOutYear >= '1998';
> >
> > Hth,
> >
> > Steve
> >

The join query posted previously will return every row match combination that meets all the where clause conditions. So as long as table B has a column with the same ID value as table A you would get all the rows. If there are some rows in A or B that have no matching row in the other table then these will be left out, as specified by the where clause conditions. Any other conditions you add to the where clause will also eliminate other rows from the result set.

If you want all the products even if they have no description then you are talking about an outer join. Example in native Oracle syntax:

select A.product_id, B.description
from product a, product_desc B
where A.product_id = B.product_id(+); <== will return a null when

there is not matching row in B so that every row in A is returned. See your manual for whatever tool you are using for how to code an outer join.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com
http://www.deja.com/