August 23, 2013 at 5:25 am
following query works
SELECT matltran.trans_num, matltran.trans_type, matltran.trans_date, matltran.ITEM,matltran.qty,matltran.matl_cost ,ledger.dom_amount,
(SELECT TOP 1 bmatltran.ref_num FROM dbo.matltran AS bmatltran WHERE bmatltran.trans_type = 'f' AND bmatltran.lot = dbo.matltran.lot) AS jobjob ,
FROM dbo.matltran
But I want to do a Join on another table based on the result of field jobjob
(SELECT TOP 1 bmatltran.ref_num FROM dbo.matltran AS bmatltran WHERE bmatltran.trans_type = 'f' AND bmatltran.lot = dbo.matltran.lot) AS jobjob ,
SELECT matltran.trans_num, matltran.trans_type, matltran.trans_date, matltran.ITEM,matltran.qty,matltran.matl_cost ,ledger.dom_amount,
(SELECT TOP 1 bmatltran.ref_num FROM dbo.matltran AS bmatltran WHERE bmatltran.trans_type = 'f' AND bmatltran.lot = dbo.matltran.lot) AS jobjob ,
FROM dbo.matltran
join rs_qcrcvr on jobjob = rs_qcrcvr.job
August 23, 2013 at 5:44 am
Are you able to provide DDL, sample data and desired output, as per the link in my signature?
Can I also suggest that you read up on column aliases - it might make things more readable.
So instead of
select reallylongtablename.col1, reallylongtablename.col2
from dbo.reallylongtablename
you get
select t1.col1, t2.col2
from dbo.reallylongtablename t1
and the icing on the cake is to surround the T-SQL with IFCode tags:
select t1.col1, t2.col2
from dbo.reallylongtablename t1
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 23, 2013 at 5:57 am
Here is the simplified query
select
m.trans_num ,
m.trans_type ,
m.lot
from matltran m
/*I want to find the top 1 record from same tables with 'WHERE trans_type = 'F' and lot = m.lot */
inner join top 1 record of matltran table where trans_type = 'F' and lot = m.lot
August 23, 2013 at 7:50 am
skb 44459 (8/23/2013)
Here is the simplified queryselect
m.trans_num ,
m.trans_type ,
m.lot
from matltran m
/*I want to find the top 1 record from same tables with 'WHERE trans_type = 'F' and lot = m.lot */
inner join top 1 record of matltran table where trans_type = 'F' and lot = m.lot
Top 1 as ordered by what?
I don't even begin to understand what you mean by joining to the top 1 row. We don't join to rows, you join to a table. Why would you want to select 1 row and join it to itself? I think it would serve you well to post some solid details and a clear description of what you are trying to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply