Select value from parent table.

• Ashwin M N

  Hall of Fame

  Points: 3668

  More actions

  June 19, 2008 at 8:26 am

  #192542

  CREATE TABLE TEST1 (B1 int PRIMARY KEY,B2 varchar(20))

  CREATE TABLE test (A1 int,A2 int REFERENCES TEST1(B1),A3 datetime)

  INSERT INTO TEST1(B1,B2)

  Select 1,'Red'

  union

  Select 2,'Green'

  union

  Select 3,'White'

  union

  Select 4,'Yellow'

  union

  Select 5,'Orange'

  union

  Select 6,'Pink'

  INSERT INTO TEST (A1,A2,A3)

  SELECT 100,3,'2007-02-06'

  UNION ALL

  SELECT 246,1,'2006-05-24'

  UNION ALL

  SELECT 200,6,'2007-02-04'

  UNION ALL

  SELECT 250,2,'2008-05-09'

  UNION ALL

  SELECT 100,2,'2008-02-28'

  UNION ALL

  SELECT 246,4,'2007-11-06'

  UNION ALL

  SELECT 250,5,'2007-01-13'

  UNION ALL

  SELECT 200,3,'2008-01-13'

  UNION ALL

  SELECT 100,5,'2006-04-12'

  UNION ALL

  SELECT 200,1,'2008-06-16'

  UNION ALL

  SELECT 246,3,'2007-07-17'

  UNION ALL

  SELECT 100,2,'2007-01-22'

  UNION ALL

  SELECT 250,1,'2007-09-17'

  UNION ALL

  SELECT 200,2,'2007-12-01'

  select * from test order by 1,3

  select *from test1

  I want a query which gives the output of value for A1 from table Test for the max date for each A1

  the corresponding value of B2 from test1 table.

  Ex: A1 B3

  100 Green

  200 Red

  246 Yellow

  250 Green

  DROP TABLE TEST

  go

  DROP TABLE TEST1

  go

• GSquared

  SSC Guru

  Points: 260824

  More actions

  June 19, 2008 at 10:38 am

  #831233

  Try this:

  ;with LastTest (LTA2, LTA3) as

  (select a2, max(a3)

  from dbo.test

  group by a2)

  select A1, B2

  from dbo.Test1

  inner join dbo.Test

  on test1.B1 = test.a2

  inner join LastTest

  on test.a2 = lta2

  and test.a3 = lta3

  (That's SQL 2005. The SQL 2000 version, move the CTE down to the From clause.)

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• raja_saminathan

  SSCommitted

  Points: 1607

  More actions

  June 19, 2008 at 11:02 pm

  #831482

  you use this query also

  select A1,B2 from Test

  inner join test1 on test.A2 = test1.b1

  where a3 in (select max(a3) from test group by a1)

  order by a1

  rajesh

• Ashwin M N

  Hall of Fame

  Points: 3668

  More actions

  June 19, 2008 at 11:04 pm

  #831484

  The logic is not working.I am getting wrong results.

• Ashwin M N

  Hall of Fame

  Points: 3668

  More actions

  June 19, 2008 at 11:14 pm

  #831489

  Sorry the first logic was wrong the second is working fine.

  Thanks