Solved

SQL Embedded Sub-Query

Posted on 2016-07-29
11
75 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 34

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 500 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

728 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