Correlated query returning only 1 record & repeat

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Correlated query returning only 1 record & repeat

Author

Topic

bretedward
Starting Member

7 Posts

Posted - 2014-08-10 : 17:48:06

I have SQL query/dual sub-query in MS Access that is returning data from the left side of the query FROM correctly, but is only returning one record from the right side of the query FROM. Furthermore, it repeats the display of the one record and it repeats the entire results set with a different one record each time until all the records have been displayed. I expect that problems described as “Furthermore” will not exist by fixing the one record issue. I have tried using all the join types available in MS Access, but none change the result.

The desired output is:
Yellow Blue
11/23/2013 11/19/2013
11/19/2103 10/01/2012
10/01/2102 10/08/2010
10/08/2010 12/14/2007
The actual output is:
Yellow Blue
11/23/2013 11/19/2013
11/19/2103 11/19/2013
10/01/2102 11/19/2013
10/08/2010 11/19/2013
11/23/2013 10/01/2102
11/19/2103 10/01/2102
10/01/2102 10/01/2102
10/08/2010 10/01/2102
The same pattern is repeated 2 more times with Blue values of 10/08/2010 and then 12/14/2007.

Here is the SQL:

SELECT Long_List.Yellow,Short_List.Blue
FROM
(
SELECT DISTINCT BirthDate AS Blue
FROM (
SELECT DISTINCT BirthDate FROM citizens
UNION
SELECT DISTINCT DeathDate FROM citizens
WHERE DeathDate IS NOT NULL
)
WHERE BirthDate <(
SELECT MAX(Pink)
FROM
(

SELECT DISTINCT BirthDate AS Pink
FROM (
SELECT DISTINCT BirthDate FROM citizens
UNION
SELECT DISTINCT DeathDate FROM citizens
WHERE DeathDate IS NOT NULL
)
)
)
ORDER BY BirthDate DESC
) AS Short_List
,
(
SELECT DISTINCT BirthDate AS Yellow
FROM (
SELECT DISTINCT BirthDate FROM citizens
UNION
SELECT DISTINCT DeathDate FROM citizens
WHERE DeathDate IS NOT NULL
)
WHERE BirthDate > (
SELECT MIN(Red)
FROM
(

SELECT DISTINCT BirthDate AS Red
FROM (
SELECT DISTINCT BirthDate FROM citizens
UNION
SELECT DISTINCT DeathDate FROM citizens
WHERE DeathDate IS NOT NULL
)
)
)
ORDER BY BirthDate DESC
) AS Long_List

ORDER BY Short_List.Blue DESC,Long_List.Yellow DESC

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-10 : 18:48:04

duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=195644

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources