We help IT Professionals succeed at work.

SQL syntax

mpdillon
mpdillon asked
on
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
Comment
Watch Question

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
<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
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'

Author

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?

Commented:
Remove the T. alias from the subquery fields.  
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>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.
 

Author

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 Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Thanks for the grade.  Good luck with your project.  -Jim