时间戳

首页 > 解决方案 > SQL Get all records from table 1 based on criteria 2 if part of result of Query 1 Criteria 1 (Access)

sql - SQL Get all records from table 1 based on criteria 2 if part of result of Query 1 Criteria 1 (Access)

问题描述

I am trying to do an operation on one table where I run an initial Query(Query1) which pulls data from Table 1 based on a condition. This Subquery is then used against Table 1 to get all records in Query 1 plus all Records in Table 1 which match a second criteria.

This is because I have many records per individual in Table 1 and I need all of them as long as one record matches the criteria from Query 1. I have tried many different queries to no avail. The closest I have thus far is: 

SELECT A.*
FROM 
Table1 A
INNER JOIN Query1 B on A.[ID] = B.[ID]

And Query1 looks like:

SELECT * FROM Table1 
WHERE [Last Updated Date] >=#4/1/2018 4:00:00 AM# 
And [Last Updated Date] <=#5/1/2018 4:00:00 AM#

The end result I want is something like

Table 1

ID  Last Updated
1   1-Jan-18
2   6-May-18
2   3-Jan-18
2   5-Apr-18
3   6-Apr-18
4   5-May-18

Query Result: 

ID  Last Updated
2   6-May-18
2   3-Jan-18
2   5-Apr-18
3   6-Apr-18

The query I wrote gives errors and I end up with over 3x the total records so clearly something is going wrong. Any help is appreciated.

标签： sqlms-access

解决方案

您没有指定您尝试执行的操作，但这将为您提供Table1符合您条件的所有记录。

SELECT * FROM Table1 WHERE ID IN (
  SELECT ID FROM Table1 
  WHERE [Last Updated Date] >=#4/1/2018 4:00:00 AM# 
  And [Last Updated Date] <=#5/1/2018 4:00:00 AM# )

推荐阅读