SQL syntax

I have two tables with a one to many relationship. IMItmIdx_SQL (master item record, one each) and IMInvTrx_SQL (Item transaction records, many per item). I want to return information from both tables. I want the Item master record and the most recent Transacton record.
In the past when I wanted to do this, I would write a query like the one below.
SELECT     item_no,
  (SELECT TOP 1 T.trx_dt  FROM IMINVTRX_SQL T
   WHERE      (T.item_no = Item_No) AND (T.source = 'R') AND (T.doc_type = 'R')
   ORDER BY T.trx_dt DESC) AS TrxDt
FROM   IMITMIDX_SQL
WHERE  (item_no = '10-803-00')

The limitation with this syntax is that I can only return the one field from the Transaction table, Trx_Dt. In this instance I need to return more than one field from the Transactoin table. I need to return, Trx_Dt, Quantity, Unit_cost.

What is the best way to accomplish this in SQL 2000?

Thanks,
pat
mpdillonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<pseudo code>

SELECT m.Column1, m.Column2, t.Column1, t.Column2
FROM IMImIdx_SQL m
INNER JOIN (
   SELECT t.ForeighKeyToMTable, t.Column1, t.Column2, Max(trx_id) as max_trx_id
   FROM IMInvTrx_SQL
   GROUP BY t.ForeighKeyToMTable, t.Column1, t.Column2) t ON m.YourIDColumn = t.ForeighKeyToMTable
0
santhimurthydCommented:
Check this

SELECT I.item_no, T.trx_dt , T.Quantity, T.Unit_cost
FROM   IMITMIDX_SQL I
Inner JOIn IMINVTRX_SQL T on T.item_no = I.Item_No
where T.source = 'R' AND T.doc_type = 'R'
I.item_no = '10-803-00'

0
mpdillonAuthor Commented:
JimHorn,
I see what you are saying. I am having trouble with the exact syntax
My code is:

SELECT     M.item_no, T.Trx_Dt
FROM         IMITMIDX_SQL M
INNER JOIN (SELECT T.Item_No, MAX(T.trx_dt) AS Trx_Dt
            FROM  IMINVTRX_SQL
            WHERE (T.source = 'R') AND (T.doc_type = 'R')
            GROUP BY T.Item_No,T.Trx_Dt) T  
ON M.item_no = T.Item_No
WHERE     (M.item_no = '10-803-00')

In Query Analyzer the error returned is:
"Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'T' does not match with a table name or alias name used in the query."

Suggestions?

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tim_csCommented:
Remove the T. alias from the subquery fields.  
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>INNER JOIN (SELECT T.Item_No, MAX(T.trx_dt) AS Trx_Dt
There is one non-aggregated column(T.Item_No)  in the above SELECDT ...

>            GROUP BY T.Item_No,T.Trx_Dt) T  
... so you should have only T.Item_No in the Group By clause, so lose the T.Trx_Dt here.

>The column prefix 'T' does not match with a table name or alias name used in the query."
The main query is correct, but in your subquery you are also using a table alias T, but not spelling out what T is, like --> FROM  IMINVTRX_SQL T
Recommend you lose the T. reference in the subquery, since it's only using one table so an alias is not necessary.
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mpdillonAuthor Commented:
jimhorn,
Thank you. That worked.
I am posting below for my own future reference.

SELECT     M.item_no, T.Trx_Dt
FROM         IMITMIDX_SQL M
INNER JOIN (SELECT Item_No, MAX(trx_dt) AS Trx_Dt
            FROM  IMINVTRX_SQL
            WHERE (source = 'R') AND (doc_type = 'R')
            GROUP BY Item_No) T  
ON M.item_no = T.Item_No
WHERE     (M.item_no = '10-803-00')
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.