Select first record from a join  
Author Message
tvubgn64





PostPosted: Fri Oct 14 05:11:42 CDT 2005 Top

SQL Server Developer >> Select first record from a join

Hello

I have a stupid newbee question, but i cannot get this query running :.-(

I have a table with "exchange codes" (Key: FromDate and exchangeCode)
And i have a table from where i need to find the "exchange rate"

I join the tables like this
SELECT EXCHRATES.EXCHRATE,
GK_FKFact.IdInvoice
FROM GK_FKFact INNER JOIN
EXCHRATES ON
GK_FKFact.Fragt_ExchangeCode =
EXCHRATES.CURRENCYCODE AND
GK_FKFact.Fragt_CreateDate <=
EXCHRATES.FROMDATE
GROUP BY EXCHRATES.EXCHRATE,
GK_FKFact.IdInvoice,
GK_FKFact.Fragt_Currency
HAVING (NOT (GK_FKFact.Fragt_Currency = 0))
ORDER BY GK_FKFact.IdInvoice

My problem is, that i only need the first record that is found in the search.
But if i se TOP 1 i only get one row not the joined tables

Hope that someone can help

Thanks on advance :-))

SQL Server306  
 
 
Jens





PostPosted: Fri Oct 14 05:11:42 CDT 2005 Top

SQL Server Developer >> Select first record from a join What about (untested, due to no DDL ro sample data :-D)

SELECT
EXCHRATE,
IdInvoice,
Fragt_Currency
FROM
(
SELECT
(
SELECT TOP 1 EXCHRATE
FROM EXCHRATES
WHERE
G1.Fragt_ExchangeCode
= EXCHRATES.CURRENCYCODE AND
G1.Fragt_CreateDate <=
EXCHRATES.FROMDATE
)
AS EXCHRATE,
G1.IdInvoice,
G1.Fragt_Currency
FROM GK_FKFact G1
WHERE NOT (G1.Fragt_Currency = 0)
) SubQuery
GROUP BY
EXCHRATE,
IdInvoice,
Fragt_Currency


HTH, jens Suessmeyer.

 
 
Graves





PostPosted: Mon Oct 17 00:47:02 CDT 2005 Top

SQL Server Developer >> Select first record from a join Hello Jens

Thanks for the sample, worked perfect :-)))

/Graves