Help with query
Dylan Reinhold asked for a query that finds rows with description 'Other', then uses their link value to return the matching id and description rows from the same item table. Christopher Erdal suggested a self-join on item. Art Kagel offered a join variant. Dylan confirmed Erdal's query worked. Solved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I hope some one can help me with this. I have a table (item) with the following columns (id, desc, link) and others that dont matter for this. Some sample data in the table is id desc link --- ----- ----- 002 ITEM1 000 003 ITEM2 000 901 Other 002 902 Other 003 Now I need to search for the decr of "Other" then take the "link" number (002 & 003) and find the matching "id" as in "ITEM1" & "ITEM2" and show the id & decr. eg: I would get back ----- 002 ITEM1 003 ITEM2 I hope this makes sense. Thanks, Dylan
Dylan Reinhold <Dylan@non.none> wrote in
news:3EC419A0.425FF452@non.none:
> I hope some one can help me with this. I have a table (item) with the
> following columns (id, desc, link) and others that dont matter for
> this.
> Some sample data in the table is
> id desc link
> --- ----- -----
> 002 ITEM1 000
> 003 ITEM2 000
> 901 Other 002
> 902 Other 003
>
> Now I need to search for the decr of "Other" then take the "link"
> number (002 & 003) and find the matching "id" as in "ITEM1" & "ITEM2"
> and show the id & decr. eg: I would get back
> -----
> 002 ITEM1
> 003 ITEM2
>
> I hope this makes sense.
>
> Thanks,
> Dylan
>
>
>
try:
SELECT A.id, A.desc
FROM item A, item B
WHERE B.desc = "Other"
AND A.id = B.link
ORDER BY id
chris
(I've not tested it)
On Thu, 15 May 2003 18:48:45 -0400, Dylan Reinhold wrote:
SELECT *
FROM item a, description d, item b
WHERE a.link != 0
AND a.link = b.id
AND b.link = 0
AND a.desc = d.desc;
-- OR in ANSI Syntax:
SELECT *
FROM item a
JOIN description d ON a.desc = d.desc
JOIN item b ON a.link = b.id AND a.link != 0 AND b.link = 0;
Art S. Kagel
> I hope some one can help me with this. I have a table (item) with the
> following columns (id, desc, link) and others that dont matter for
> this.
> Some sample data in the table is
> id desc link
> --- ----- -----
> 002 ITEM1 000
> 003 ITEM2 000
> 901 Other 002
> 902 Other 003
>
> Now I need to search for the decr of "Other" then take the "link" number
> (002 & 003) and find the matching "id" as in "ITEM1" & "ITEM2" and show
> the id & decr. eg: I would get back
> -----
> 002 ITEM1
> 003 ITEM2
>
> I hope this makes sense.
>
> Thanks,
> Dylan
"Christopher J. Erdal" wrote:
> Dylan Reinhold <Dylan@non.none> wrote in
> news:3EC419A0.425FF452@non.none:
>
> > I hope some one can help me with this. I have a table (item) with the
> > following columns (id, desc, link) and others that dont matter for
> > this.
> > Some sample data in the table is
> > id desc link
> > --- ----- -----
> > 002 ITEM1 000
> > 003 ITEM2 000
> > 901 Other 002
> > 902 Other 003
> >
> > Now I need to search for the decr of "Other" then take the "link"
> > number (002 & 003) and find the matching "id" as in "ITEM1" & "ITEM2"
> > and show the id & decr. eg: I would get back
> > -----
> > 002 ITEM1
> > 003 ITEM2
> >
> > I hope this makes sense.
> >
> > Thanks,
> > Dylan
> >
> >
> >
> try:
>
> SELECT A.id, A.desc
> FROM item A, item B
> WHERE B.desc = "Other"
> AND A.id = B.link
> ORDER BY id>
> chris
>
> (I've not tested it)
>
Thanks I got it working
Dylan