?
Solved

SQL Embedded Sub-Query

Posted on 2016-07-29
11
Medium Priority
?
78 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 35

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
Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

 
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

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!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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
Suggested Courses

801 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