CODEWITHSUNDEEP
sqlms-accesssubquery
virrion
virrion

Reputation: 424

MS Access Query (two tables only)

I have two tables:

**T1**
ID |Transaction| amount |
1  |trans1     | 97     |
1  |trans2     | 22     |
2  |trans7     | 98     |


**T2** 
ID |description|spec|
1  |Item 1     |text|
2  |Item 2     |zip |

Note: ID columns in both tables are THE SAME. However, ID in table T1 is not unique.

I need query to retrieve data from tables but only row with highest amount (table T1). Example result:

ID|description|spec|transaction|amount|
1 |item1      |text| trans1    |  97  | 
2 |item2      |zip | trans7    |  98  |

Please help!

Thank you.

Upvotes: 1

Views: 125

Answers (2)

Sablefoste
Sablefoste

Reputation: 4192

Try the following query:

SELECT ID, description, spec, `transaction` , amt
FROM T2
JOIN
  (SELECT ID, `transaction`, MAX(amount) AS amt FROM T1 GROUP BY ID) AS T3
ON T3.ID=T2.ID

watch out, because the word transaction may be a reserved keyword. You could always enclose in tick marks (like above)

Upvotes: 0

Ali
Ali

Reputation: 949

You should use this query to get one row for each ID in the T2

SELECT ID, MAX(AMOUNT) AS AMT FROM T1 GROUP BY ID

Then join the first table into it and select all that you need. So it would look like something like this:

SELECT T2.ID,
  description,
  spec,
  transaction ,
  AMT
FROM T2
JOIN
  (SELECT ID, MAX(AMOUNT) AS AMT FROM T1 GROUP BY ID
  ) T3
ON T3.ID=T2.ID;

Note: I used Oracle, in your db you might need to modify the query in order to resolve keyword conflicts.

Upvotes: 1

Related Questions