Postgres Pro
Downloads
Home   >   mailing lists

Thread: help w/ query

help w/ query

From
"G.L. Grobe"
Date:
Anyone help w/ the following sql query ... Here's my tables ...

table A
id      name
-----|-------
1000 AA
1001  AB
1002 AC
1003 AD

table B
id  pid
-----|-------
1000 1000
1001 1000
1002 1000
1003 1001
1004 1001

Here's my query, (note that 'AA' is all the info I provide,
cause that's the ) ...

SELECT B.id FROM B, A WHERE B.pid = A.id AND A.name = 'AA';

Returns the following resultset ...

id (note, this is from B, but all have pid's of 1000)
------
1000
1001
1002

When what I really want returned is the 1000 from B's table
and only that record ...

id (note, this is what I want from B)
------
1000

Any idea how to grab this?

TIA

Re: help w/ query

From
Andrew Gould
Date:
The relationship between B.id and B.pid is not
specific enough to accomplish your goal.  You need to
modify your data model.  Without understanding the
purpose your database and the meaning of its fields,
it's hard to give more specific guidance.

Best of luck,

Andrew

--- "G.L. Grobe" <gary@grobe.net> wrote:
> Anyone help w/ the following sql query ... Here's my
> tables ...
>
> table A
> id      name
> -----|-------
> 1000 AA
> 1001  AB
> 1002 AC
> 1003 AD
>
> table B
> id  pid
> -----|-------
> 1000 1000
> 1001 1000
> 1002 1000
> 1003 1001
> 1004 1001
>
> Here's my query, (note that 'AA' is all the info I
> provide,
> cause that's the ) ...
>
> SELECT B.id FROM B, A WHERE B.pid = A.id AND A.name
> = 'AA';
>
> Returns the following resultset ...
>
> id (note, this is from B, but all have pid's of
> 1000)
> ------
> 1000
> 1001
> 1002
>
> When what I really want returned is the 1000 from
> B's table
> and only that record ...
>
> id (note, this is what I want from B)
> ------
> 1000
>
> Any idea how to grab this?
>
> TIA

__________________________________________________
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com

Re: help w/ query

From
"Ernesto Baschny"
Date:
On 19 Oct 2001 at 19:32, G.L. Grobe wrote:

> Anyone help w/ the following sql query ... Here's my tables
...
>
> table A
> id      name
> -----|-------
> 1000 AA
> 1001  AB
> 1002 AC
> 1003 AD
>
> table B
> id  pid
> -----|-------
> 1000 1000
> 1001 1000
> 1002 1000
> 1003 1001
> 1004 1001
>
> Here's my query, (note that 'AA' is all the info I provide,
> cause that's the ) ...
>
> SELECT B.id FROM B, A WHERE B.pid = A.id AND A.name = 'AA';

I think you misspelled the first parameter of the first
WHERE condition. It should be  "B.id", not "B.pid":

  SELECT B.id FROM B, A WHERE B.id = A.id AND A.name = 'AA';


--
Ernesto Baschny <ernst@baschny.de>
 http://www.baschny.de - PGP Key:
http://www.baschny.de/pgp.txt
 Sao Paulo/Brasil - Stuttgart/Germany
 Ernst@IRCnet - ICQ# 2955403