Joining tables

Hi,
I have a table A like:
id
--
1
2
3
and a table B like:
id type
-- --
1 lion
1 tyger
2 donkey
3 lion
3 tiger
3 monkey
I'd like to return a table C like:
1 lion
2 donkey
3 lion
i.e., join A and B but only get the first row of multi rows. I don't want to
use cursors if I can. Is there anyway to manipulate joins and nested selects
to do this?
Thx, MohebOn Thu, 10 Mar 2005 14:31:11 -0800, Moheb wrote:
(snip)
>I'd like to return a table C like:
>1 lion
>2 donkey
>3 lion
>i.e., join A and B but only get the first row of multi rows.
(snip)
Hi Moheb,
Try
SELECT A.id, MIN(B.type)
FROM A
INNER JOIN B
ON B.id = A.id
GROUP BY A.id
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||No, because in SQL, rows are not intrinsically ordered. To get the "First"
record, in a group, you have to have some way of defining what you mean by
"first". The database doesn't know (Unless you store a created datetime in
each row) when the row was created, so it doesn't have any way of sequencing
them, except based on the data in the rows...
So you could get the lowest (or highest) alphabetical record for each ID,
but that's not what you want, I think. (coincidentally, the order happens t
o
be alphabetical as you have it listed...)
To get what you want, you need to add a Created datetime to the second
table, and then query
Select id, type From TableB B
Where CreatedDT =
(Select Min(CreatedDT)
From TableB
Where ID = B.ID)
"Moheb" wrote:

> Hi,
> I have a table A like:
> id
> --
> 1
> 2
> 3
> and a table B like:
> id type
> -- --
> 1 lion
> 1 tyger
> 2 donkey
> 3 lion
> 3 tiger
> 3 monkey
> I'd like to return a table C like:
> 1 lion
> 2 donkey
> 3 lion
> i.e., join A and B but only get the first row of multi rows. I don't want
to
> use cursors if I can. Is there anyway to manipulate joins and nested selec
ts
> to do this?
> Thx, Moheb
>|||Hugo,
Thanks for your reply. It almost solves it however your Min clause finds the
minimum "alphabetically" in the type column. How can I get the first row of
multi rows returned by a.id = b.id? Thx, Moheb
"Hugo Kornelis" wrote:

> On Thu, 10 Mar 2005 14:31:11 -0800, Moheb wrote:
> (snip)
> (snip)
> Hi Moheb,
> Try
> SELECT A.id, MIN(B.type)
> FROM A
> INNER JOIN B
> ON B.id = A.id
> GROUP BY A.id
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Thu, 10 Mar 2005 14:59:05 -0800, Moheb wrote:

>Hugo,
>Thanks for your reply. It almost solves it however your Min clause finds th
e
>minimum "alphabetically" in the type column. How can I get the first row of
>multi rows returned by a.id = b.id? Thx, Moheb
Hi Moheb,
A table in a relational database is, by definition, an UNordered set of
rows. There is no concept of a "first" row, only a concept of "first row
when ordered by ...". The sample you posted indicated you wanted the
first when alphabetically ordered by type.
Check if CBretana's post helps you further. If it doesn't then please
indicate in a reply how we can determine which row is "first".
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||You and Hugo are both right. To tell the truth, I had done something like
this before and wasn't able to locate it. Upon close examination, it appeare
d
that the columns I wrote for were "sortable" (number, date, alphabetical,
etc.). Thx for the replies.
"CBretana" wrote:
> No, because in SQL, rows are not intrinsically ordered. To get the "Firs
t"
> record, in a group, you have to have some way of defining what you mean by
> "first". The database doesn't know (Unless you store a created datetime i
n
> each row) when the row was created, so it doesn't have any way of sequenci
ng
> them, except based on the data in the rows...
> So you could get the lowest (or highest) alphabetical record for each ID,
> but that's not what you want, I think. (coincidentally, the order happens
to
> be alphabetical as you have it listed...)
> To get what you want, you need to add a Created datetime to the second
> table, and then query
> Select id, type From TableB B
> Where CreatedDT =
> (Select Min(CreatedDT)
> From TableB
> Where ID = B.ID)
> "Moheb" wrote:
>