Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Embedded Sub-Query

Posted on 2016-07-29
11
Medium Priority
?
86 Views
Last Modified: 2016-08-01
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...
0
Comment
Question by:sparker1970
  • 6
  • 4
11 Comments
 
LVL 36

Expert Comment

by:ste5an
ID: 41735258
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41735264
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
 

Author Comment

by:sparker1970
ID: 41736498
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 49

Expert Comment

by:PortletPaul
ID: 41736799
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
 

Author Comment

by:sparker1970
ID: 41736932
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 41737028
>>"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
 

Author Comment

by:sparker1970
ID: 41737339
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
 

Author Comment

by:sparker1970
ID: 41737350
Full query attached
LJD-Query.txt
0
 

Author Comment

by:sparker1970
ID: 41737803
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
 

Author Closing Comment

by:sparker1970
ID: 41737807
Your response got me the closest to my final result
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41738115
thanks.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question