Prev: login via ad groups - query to find this info.
Next: from a stored procedure: how to return datasets and their tabl
From: James on 22 Jul 2008 06:32
I have 2 tables, A and B with a layout similar to the following:

A
PKId | data
1 | aa


B
FKId | year | data
1 | 2005 | xx
1 | 2006 | yy
1 | 2006 | zz


What I am trying to do is join the 2 tables only for the minimum year
(2005 in this case).

So I would have results like
A.data | B.data
aa | xx


Struggling with the sql though.

This works
SELECT a.PKId, MIN(b.year)
FROM A a, B b
WHERE A.pkid = b.fkid
GROUP BY a.PkId

But If I try and get 'data' from B it starts selecting every
corresponding row.

ie
SELECT a.PKId, MIN(b.year), b.data
FROM A a, B b
WHERE A.pkid = b.fkid
GROUP BY a.PkId, b.data

So I would end up with 3 rows based on the Id=1.

Any ideas for me? Probably something dead simple...


From: vinu on 22 Jul 2008 06:47
james

try this

create table #A(PKID int ,data varchar(10))
create table #B(FKID int,Year1 int ,data varchar(10))

insert into #a values(1,'aa')
insert into #b values(1,2005,'xx')
insert into #b values(1,2005,'cc')
insert into #b values(1,2006,'yy')
insert into #b values(1,2006,'xx')


select A.data,b.data from #a a
inner join #b b on a.PKID=b.FKID
where b.year1 =(select min(year1) from #b)

vinu


"James" <jamesb457(a)gmail.com> wrote in message
news:52372491-296c-47e8-8e26-564c82a15f03(a)x35g2000hsb.googlegroups.com...
>I have 2 tables, A and B with a layout similar to the following:
>
> A
> PKId | data
> 1 | aa
>
>
> B
> FKId | year | data
> 1 | 2005 | xx
> 1 | 2006 | yy
> 1 | 2006 | zz
>
>
> What I am trying to do is join the 2 tables only for the minimum year
> (2005 in this case).
>
> So I would have results like
> A.data | B.data
> aa | xx
>
>
> Struggling with the sql though.
>
> This works
> SELECT a.PKId, MIN(b.year)
> FROM A a, B b
> WHERE A.pkid = b.fkid
> GROUP BY a.PkId
>
> But If I try and get 'data' from B it starts selecting every
> corresponding row.
>
> ie
> SELECT a.PKId, MIN(b.year), b.data
> FROM A a, B b
> WHERE A.pkid = b.fkid
> GROUP BY a.PkId, b.data
>
> So I would end up with 3 rows based on the Id=1.
>
> Any ideas for me? Probably something dead simple...
>
>


From: James on 22 Jul 2008 08:51
On 22 Jul, 11:47, "vinu" <vinu.t.1...(a)gmail.com> wrote:
> james
>
> try this
>
> create table #A(PKID int ,data varchar(10))
> create table #B(FKID int,Year1 int ,data varchar(10))
>
> insert into #a values(1,'aa')
> insert into #b values(1,2005,'xx')
> insert into #b values(1,2005,'cc')
> insert into #b values(1,2006,'yy')
> insert into #b values(1,2006,'xx')
>
> select A.data,b.data from #a a
> inner join #b b on a.PKID=b.FKID
> where b.year1 =(select min(year1) from #b)
>
> vinu
>
> "James" <jamesb...(a)gmail.com> wrote in message
>
> news:52372491-296c-47e8-8e26-564c82a15f03(a)x35g2000hsb.googlegroups.com...
>
> >I have 2 tables, A and B with a layout similar to the following:
>
> > A
> > PKId  | data
> >  1     |  aa
>
> > B
> > FKId  |  year  |  data
> >  1     |  2005 |   xx
> >  1     |  2006 |   yy
> >  1     |  2006 |   zz
>
> > What I am trying to do is join the 2 tables only for the minimum year
> > (2005 in this case).
>
> > So I would have results like
> > A.data  |  B.data
> >  aa      |    xx
>
> > Struggling with the sql though.
>
> > This works
> > SELECT a.PKId, MIN(b.year)
> > FROM   A a, B b
> > WHERE   A.pkid = b.fkid
> > GROUP BY a.PkId
>
> > But If I try and get 'data' from B it starts selecting every
> > corresponding row.
>
> > ie
> > SELECT a.PKId, MIN(b.year), b.data
> > FROM   A a, B b
> > WHERE   A.pkid = b.fkid
> > GROUP BY a.PkId, b.data
>
> > So I would end up with 3 rows based on the Id=1.
>
> > Any ideas for me? Probably something dead simple...

Cheers that is just what I needed!

Last question for now:
Is it possible for it to only select 1 row from table B.

The only field I am interested in in table B will remain constant with
the year.

So for the exmple you gave running it owuld return 1 row
ID = 1, Year = 2005, Data = "xx"
From: Plamen on 22 Jul 2008 08:52
Here are two ways:

SELECT data, [year], b_data
FROM (SELECT A.data, B.[year], B.data AS b_data,
ROW_NUMBER() OVER(PARTITION BY B.fkid
ORDER BY B.[year],
B.data) AS
seq
FROM A
JOIN B
ON A.pkid = B.fkid) AS T
WHERE seq = 1;

SELECT A.data, B.[year], B.data
FROM A
JOIN B
ON A.pkid = B.fkid
WHERE B.[year] = (SELECT MIN(B2.[year])
FROM B AS B2
WHERE B2.fkid = B.fkid);

HTH,

Plamen Ratchev
http://www.SQLStudio.com
From: James on 22 Jul 2008 09:01
On 22 Jul, 13:52, Pla...(a)sqlstudio.com wrote:
> Here are two ways:
>
> SELECT data, [year], b_data
> FROM (SELECT A.data, B.[year], B.data AS b_data,
>                     ROW_NUMBER() OVER(PARTITION BY B.fkid
>                                           ORDER BY B.[year],
>                                                          B.data) AS
> seq
>          FROM A
>         JOIN B
>           ON A.pkid = B.fkid) AS T
> WHERE seq = 1;
>
> SELECT A.data, B.[year], B.data
> FROM A
> JOIN B
>   ON A.pkid = B.fkid
> WHERE B.[year] = (SELECT MIN(B2.[year])
>                          FROM B AS B2
>                          WHERE B2.fkid = B.fkid);
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

Thanks,
you are a genius!
 |  Next  |  Last
Pages: 1 2
Prev: login via ad groups - query to find this info.
Next: from a stored procedure: how to return datasets and their tabl