SQL Embedded Sub-Query

I have my main query and I need an additional sub-query I need to embed.

I currently have this query in Access that needs to be a sub-query and return the oldest TRANS_DATE for the conditions listed

SELECT FINAN.FILENO, FINAN.TRANS_DATE
FROM FINAN INNER JOIN MASTER ON FINAN.FILENO = MASTER.FILENO
WHERE (((FINAN.CODE) Between "19" And "23" Or (FINAN.CODE)="2") AND ((FINAN.BPJ)="J") AND ((FINAN.TRUST)="TR53") AND ((FINAN.AMOUNT)>0) AND ((MASTER.JMT_DATE)<[TRANS_DATE]));

I also have a second query in Access that finds the MIN date for each FILENO and returns it to the query.

So, in plain English...

FINAN and MASTER tables link with FILENO

I need the oldest TRANS_DATE when the WHERE statement above is true

Thanks in advance...
sparker1970Asked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
>>"Am I able to slip the where statement between the FROM and GROUP BY lines? "

Yes.

Select mt.*, sq.mindate
From maintable mt
Left join (
   Select fileno, min (adate) mindate
   From othertable
 
   where ...

   Group by fileno
  ) sq on mt.fileno = sq.fileno
0
 
ste5anSenior DeveloperCommented:
Please post a concise and complete example. This includes table DDL and sample data INSERT statements as runnable T-SQL script.

Caveat:  (((FINAN.CODE) Between "19" And "23" does a lexical match. Thus "190" is between your boundaries:

SELECT IIF(  '190' BETWEEN '19' AND '23', 1, 0 );

Open in new window


returns 1.
0
 
PortletPaulfreelancerCommented:
Don't quite follow your question. Think you  want either the min date for each row or all of the rows;

SELECT FINAN.FILENO, MIN(FINAN.TRANS_DATE) min_date
FROM FINAN INNER JOIN MASTER ON FINAN.FILENO = MASTER.FILENO
WHERE (((FINAN.CODE) Between "19" And "23" Or (FINAN.CODE)="2") AND ((FINAN.BPJ)="J") AND ((FINAN.TRUST)="TR53") AND ((FINAN.AMOUNT)>0) AND ((MASTER.JMT_DATE)<[TRANS_DATE]));
GROUP BY FINAN.FILENO


SELECT MIN(FINAN.TRANS_DATE) minmindate
FROM FINAN INNER JOIN MASTER ON FINAN.FILENO = MASTER.FILENO
WHERE (((FINAN.CODE) Between "19" And "23" Or (FINAN.CODE)="2") AND ((FINAN.BPJ)="J") AND ((FINAN.TRUST)="TR53") AND ((FINAN.AMOUNT)>0) AND ((MASTER.JMT_DATE)<[TRANS_DATE]));
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
sparker1970Author Commented:
Sorry, now that I read it again I can see the confusion...

My primary query uses a table named MASTER and links to FINAN using the common field named FILENO from each table

In my primary query I need to return the earliest TRANS_DATE for each occurrence of FILENO where the following conditions are true:

WHERE (((FINAN.CODE) Between "19" And "23" Or (FINAN.CODE)="2") AND ((FINAN.BPJ)="J") AND ((FINAN.TRUST)="TR53") AND ((FINAN.AMOUNT)>0) AND ((MASTER.JMT_DATE)<[TRANS_DATE]))

thx
0
 
PortletPaulfreelancerCommented:
Cant you just use a group by query and joint that to the main table?

Im using a phone right now,  good coding is next to impossible but like this:

Select mt.*, sq.mindate
From maintable mt
Left join (
   Select fileno, min (adate) mindate
   From othertable
   Group by fileno
  ) sq on mt.fileno = sq.fileno

Now you get the minimum date for every fileno.

Please note because i find copy paste too hard via a phone any table or column names are just representative of your real names
0
 
sparker1970Author Commented:
Am I able to slip the where statement between the FROM and GROUP BY lines?

I need the MIN date based on the conditions I mentioned in my where statement
0
 
sparker1970Author Commented:
Not sure where I am going wrong with this query...

I have attached the full query in case it helps.

Here is the section I created for this post:

      SELECT mt.*, sq.MINDATE
      FROM dbo.CLS_MASTER_POOL mt
      LEFT JOIN(
            SELECT FILENO, MIN(TRANS_DATE) AS MINDATE
            FROM dbo.CLS_FINAN_POOL
            WHERE ((CODE Between "19" And "23") Or (CODE="2")) AND BPJ="J" AND TRUST="TR53" AND AMOUNT>0 AND JMT_DATE<[TRANS_DATE]
            GROUP BY FILENO
            ) sq ON mt.FILENO = sq.FILENO
0
 
sparker1970Author Commented:
Full query attached
LJD-Query.txt
0
 
sparker1970Author Commented:
Here is the final query I got to work today:

      isnull(convert(varchar,sq.MINDATE,12),'') as 'FIRST PMT AFTER JUDGMENT'
      FROM dbo.CLS_MASTER_POOL as mt
      LEFT JOIN(
            SELECT CLS_FINAN_POOL.FILENO as FILENO , MIN(TRANS_DATE) AS MINDATE
            FROM dbo.CLS_FINAN_POOL
                     left outer join dbo.CLS_MASTER_POOL on CLS_FINAN_POOL .FILENO = CLS_MASTER_POOL.FILENO
            WHERE ((CODE like '20%' or CODE like '22%' Or CODE like '2%') AND BPJ LIKE 'J' AND TRUST LIKE 'TR53' AND AMOUNT>0 AND JMT_DATE<TRANS_DATE)
            GROUP BY CLS_FINAN_POOL .FILENO
            ) sq ON mt.FILENO = sq.FILENO
0
 
sparker1970Author Commented:
Your response got me the closest to my final result
0
 
PortletPaulfreelancerCommented:
thanks.
0
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.

All Courses

From novice to tech pro — start learning today.