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 HornSQL Server Data DudeCommented:
<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
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'

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?

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

tim_csCommented:
Remove the T. alias from the subquery fields.  
Jim HornSQL Server Data DudeCommented:
>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.
 

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')
Jim HornSQL Server Data DudeCommented:
Thanks for the grade.  Good luck with your project.  -Jim
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.