November 19, 2007 at 5:02 am
Hi all,
I try to build a SQL between "X" tables.
My problem is on a JOIN...
SELECT TOP 1
ID
FROM tableB
WHERE price <= @var
ORDER BY price desc
------
SELECT
a.price
FROM tableA a
INNER JOIN TableB ON
(????????)
I don't know if this is possble with a sql...or I must build a table function....
I try this second scenario but is possible pass a value on SQL like the field of another table...without ussing variable.
I try with an cursor but take too much time long...
Thanks Alen.
November 19, 2007 at 5:19 am
SELECT a.price, x.price
FROM tableA a
INNER JOIN
(SELECT TOP 1 ID, Price
FROM tableB
WHERE price <= @var
ORDER BY price desc
) x
on a.id = x.id
something like that? (give me more information and I can do a better query for you)
Paul Ross
November 19, 2007 at 7:43 am
this is the focus of the query...there are many other table on JOIN...but belove in the example is my problem.
Must be something like under...but I can't use variables...on the fly with a JOIN is possible?
..................
SELECT TOP 1
*
FROM Consel
ORDER BY imp_fin DESC
WHERE live.tasso0 = 1
AND live.imp_fin = 4587
..................
--- part of query ----
SELECT
live.*
FROM TABLE as tzero
LEFT JOIN (
SELECT TOP 1
*
FROM Consel
ORDER BY imp_fin DESC
) as live ON
(ISNULL(SIGN(tzero.IDSito+1),0) = live.tasso0 AND imp_fin <= tzeroprezzo)
I think there is some problem 'cause I have always null for live.*
Thanks Alen Italy
November 19, 2007 at 3:27 pm
Your problem is that you arn't linking your subquery to your outer query. Your inner query is returning exactly 1 record. The top record in the table. Then you have your join after that so you are only going to get a value in the 1 case where the top row of consel (in this case the smallest value if imp_fin) cases your join to work. Try adding a where clause (I've given a sample below) to tell your subquery to give you the top 1 for each row of the outer table.
[query]
SELECT
live.*
FROM TABLE as tzero
LEFT JOIN (
SELECT TOP 1
*
FROM Consel
WHERE tzero.imp_fin = consel.imp_fin
ORDER BY imp_fin DESC
) as live ON
(ISNULL(SIGN(tzero.IDSito+1),0) = live.tasso0 AND imp_fin <= tzeroprezzo)
[/quote]
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply