Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 5th, 2005, 02:16 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 596
Thanks: 1
Thanked 3 Times in 3 Posts
Default Return from different records in same table

Hi all, this is hurting my head.
Can anybody point me in the right direction?

TABLE_A
TA_ID
TA_YEAR

TABLE_B
TA_ID
TB_VALUE

There is a one to one between TABLE_A and TABLE_B on TA_ID
TB_VALUE can re-occur.

So to different TA_ID's may have the same TB_VALUE

I want to select the TA_ID with the highest TA_YEAR and the TA_ID with the lowest TA_YEAR in one record for records that match on TB_VALUE

So
TABLE_A
TA_ID TA_YEAR
1 2003
2 2004
3 2005

TABLE_B
TA_ID TB_VALUE
1 81
2 43
3 81

The query would return a recordset
TA_ID_High TA_ID_LOW TB_VALUE
3 1 81

I cant get my head around the query at all.......
Many thanks for your time to read this. any help would be appreciated.


======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================
__________________
======================================
"They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad."
--Shakespeare
======================================
 
Old October 7th, 2005, 10:14 PM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

select x = min(A.TA_Year), y = max(A.TA_year), B.TB_Value
from Table_A A
inner join Table_B B on A.TA_ID = B.TA_ID

Have not tested it but I think this is the direction your going for.


 
Old October 10th, 2005, 08:51 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 596
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Thanks heaps Rob,
That does look like the right thing.
I'll give it a try.

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================





Similar Threads
Thread Thread Starter Forum Replies Last Post
could i join function(return table) with a table alyeng2000 SQL Server 2000 6 September 30th, 2004 07:23 AM
Query to return 37 records maha Access 3 September 10th, 2004 02:37 AM
copy and append records from table-A to table B bhunter Access 6 March 9th, 2004 02:02 PM
thousands records enter one table to another table mateenmohd SQL Server 2000 3 July 17th, 2003 07:52 AM
looping through return records in sproc jtyson SQL Server ASP 5 July 14th, 2003 10:16 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.