Re: query to pull records based of subquery column
- From: Ken Sheridan <kenwsheridan@xxxxxxxxxxx>
- Date: Sun, 28 Dec 2008 10:14:49 -0800 (PST)
On Dec 26, 2:09 pm, Mary <M...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Hello all..
I am really really in need of a help.
I have a table and a query, table1 and query1. Query1 has the following
columns and rows:
ID Audit
223 2
234 3
225 1
226 2
The column ID is unique, have one record per row and data in column Audit
are not unique.
Here are the columns and rows for table 1. Records in column ID are unique
but can have more than one record per rows. Amt Type is grouped by Trans
type. Example, cash and visa are credit Trans type; where as, all MC and TC
are paytogo.
ID Amt Amt type Trans Type
223 1.50 cash credit
223 2.03 visa credit
223 2.30 MC paytogo
234 .50 TC paytogo
234 1.75 cash credit
234 .90 visa credit
234 5.30 visa credit
234 1.25 TC paytogo
225 1.35 MC paytogo
225 2.24 visa credit
225 3.35 MC paytogo
226 2.17 visa credit
226 1.15 TC paytogo
226 1.50 TC paytogo
226 1.60 visa credit
I would like a sql statement or know how to create a query that will look at
column Audit of query1 and pull that amount of records of table1. The result
should be listed by the top Amt for the records based on query1.Audit.
The result for this query should be as shown below. There are 3 records for
ID 234, 1 record for ID 225, 2 records for ID 223 and 2 records for ID 226.
ID Amt Amt type Trans Type
234 5.30 visa credit
234 1.25 TC paytogo
234 1.75 cash credit
225 3.35 MC paytogo
223 2.30 MC paytogo
223 2.03 visa credit
226 2.17 visa credit
226 1.60 visa credit
Any help will be greatly appreciated as this has been holding be back from
completing this project. I am opened to any direction. I think this is should
query1 should be joined as a subquery of table1.
Thanks a million in advance.
Try this:
SELECT T1.*
FROM Table1 As T1
INNER JOIN Query1
ON T1.ID = Query1.ID
WHERE T1.Amt >=
(SELECT MIN(Amt)
FROM Table1 As T2
WHERE T2.ID = T1.ID
AND
(SELECT COUNT(*)
FROM Table1 AS T3
WHERE T3.ID = T2.ID
AND T3.Amt >= T2.Amt) <= Query1.Audit);
Ken Sheridan
Stafford, England
.
- Follow-Ups:
- References:
- Prev by Date: Re: OUTER JOIN Trouble
- Next by Date: response to post for "use different equations in same query field"
- Previous by thread: Re: query to pull records based of subquery column
- Next by thread: Re: query to pull records based of subquery column
- Index(es):
Relevant Pages
|
