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

General SQL Server Forums

New to SQL Server Programming

Correlated query returning only 1 record & repeat

Author

Topic

bretedward
Starting Member

7 Posts

Posted - 2014-08-10 : 17:44:47

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:45:11

You have this section repeated:


SELECT DISTINCT BirthDate FROM citizens
UNION
SELECT DISTINCT DeathDate FROM citizens
WHERE DeathDate IS NOT NULL

The first select is not constrained and will return all birthdates (including nulls). The second won't return nulls. In any case you'll get birth and death dates mixed up. Is that what you want?

To really help you though, please post:

1. CREATE TABLE commands for the citizens table
2. INSERT INTO commands to create test data in the citizens table
3. The results of running your query against the test data
4. the desired result.

You have several levels of nested queries that I feel there must be a simpler way. also, is this a pass-through query to SQL Server? If not, you might try posting on an MS Access forum.

bretedward
Starting Member

7 Posts

Posted - 2014-08-11 : 00:19:06

I realize that section was repeated.

There are no NULLs in the BirthDate column, so no constraint was needed. DeathDate may have NULLs, so a constraint was required.

Mixing of the dates is fine.

The DB is in a test environment. I did not create the table. It was there already.

Here it is the citizens table:

Name BirthDate DeathDate
Jerry Smith 12/14/2007 11/19/2013
John Johnson 10/08/2010 11/23/2013
Will Williams 10/01/2012
Sandy Sanders 10/08/2010 11/23/2013
Rosa Rosenthal 11/19/2013

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.

The desired output is:

Yellow Blue
11/23/2013 11/19/2013
11/19/2013 10/01/2012
10/01/2102 10/08/2010
10/08/2010 12/14/2007

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-11 : 08:49:32

It doesn't matter if you created the table or not. Since you did not provide CREATE TABLE and INSERT INTO statements, I had to do it myself. This is what I wanted you to do for me:


CREATE TABLE #citizens (Name varchar(50), BirthDate date, DeathDate date NULL)
INSERT INTO #citizens (Name, BirthDate, DeathDate) VALUES
('Jerry Smith', '12/14/2007', '11/19/2013'),
('John Johnson', '10/08/2010', '11/23/2013'),
('Will Williams', '10/01/2012', NULL),
('Sandy Sanders', '10/08/2010', '11/23/2013'),
('Rosa Rosenthal', '11/19/2013', NULL)

After this, I can run your query (replacing citizens with #citizens), but it has many syntax errors:

quote:

Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'WHERE'.
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'WHERE'.
Msg 102, Level 15, State 1, Line 48
Incorrect syntax near ')'.
Msg 1033, Level 15, State 1, Line 88
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Msg 156, Level 15, State 1, Line 117
Incorrect syntax near the keyword 'ORDER'.

The first error is because you need to alias the preceding subquery. I changed it at line 10:


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
) Blue

That gets past the first error but throws a second error at line 23:

quote:

Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ')'.

Again, since you didn't alias the preceding subquery. This change fixes that:


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
) Pink

but that just pushes the error to line 24, for the same reason, so again I added an alias:


SELECT DISTINCT BirthDate AS Pink
FROM (
SELECT DISTINCT BirthDate FROM #citizens
UNION
SELECT DISTINCT DeathDate FROM #citizens
WHERE DeathDate IS NOT NULL
) Pink
) maxPink

Now we get to this error:

quote:

Msg 1033, Level 15, State 1, Line 26
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

So, I commented the ORDER BY clause since it has no meaning here.

I reformatted your query for readability and got this:


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
		) Blue
	WHERE BirthDate < (
			SELECT MAX(Pink) maxPink
			FROM (
				SELECT DISTINCT BirthDate AS Pink
				FROM (
					SELECT DISTINCT BirthDate
					FROM #citizens
					
					UNION
					
					SELECT DISTINCT DeathDate
					FROM #citizens
					WHERE DeathDate IS NOT NULL
					) Pink
				) maxPink
			)
		--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
			) Yellow
		WHERE BirthDate > (
				SELECT MIN(Red) minRed
				FROM (
					SELECT DISTINCT BirthDate AS Red
					FROM (
						SELECT DISTINCT BirthDate
						FROM #citizens
						
						UNION
						
						SELECT DISTINCT DeathDate
						FROM #citizens
						WHERE DeathDate IS NOT NULL
						) Red
					) minRed
				)
			-- ORDER BY BirthDate DESC
		) AS Long_List
ORDER BY Short_List.Blue DESC
	,Long_List.Yellow DESC

Then ran it and got this:


Yellow	Blue
2013-11-23	2013-11-19
2013-11-19	2013-11-19
2012-10-01	2013-11-19
2010-10-08	2013-11-19
2013-11-23	2012-10-01
2013-11-19	2012-10-01
2012-10-01	2012-10-01
2010-10-08	2012-10-01
2013-11-23	2010-10-08
2013-11-19	2010-10-08
2012-10-01	2010-10-08
2010-10-08	2010-10-08
2013-11-23	2007-12-14
2013-11-19	2007-12-14
2012-10-01	2007-12-14
2010-10-08	2007-12-14

Finally, I changed the first line to:


SELECT max(Long_List.Yellow) Yellow ,Short_List.Blue

and change the end of the query to:


group by Short_List.Blue
ORDER BY Short_List.Blue DESC

Running this I got:


Yellow	Blue
2013-11-23	2013-11-19
2013-11-23	2012-10-01
2013-11-23	2010-10-08
2013-11-23	2007-12-14

Which is close to what you want. Now, can you describe the desired results in the first column (I guess I don't get what you're after) and lets see if we can finish this problem)

bretedward
Starting Member

7 Posts

Posted - 2014-08-11 : 10:58:49

Thanks for all your hard work at this.

The bad news is that the end result still has the same problem (just moved from 1 column to the other - before, the Blue column was repeating the 11/19/2013 date) that I started with, minus the repeating blocks.

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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-11 : 11:21:42

Yes, but what is the business logic for the first column? As I asked before, "can you describe the desired results in the first column"

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-08-11 : 11:28:45

Maybe:


WITH Dates(BirthDate)
AS
(
	SELECT DISTINCT
		CASE N.N
			WHEN 1 THEN BirthDate
			ELSE DeathDate
		END
	FROM #citizens C
		CROSS JOIN (SELECT 1 UNION ALL SELECT 2) N(N)
)
,OrdDates(BirthDate, rn)
AS
(
	SELECT BirthDate
		,ROW_NUMBER() OVER (ORDER BY BirthDate DESC)
	FROM Dates
	WHERE BirthDate IS NOT NULL
)
SELECT D1.BirthDate AS Yellow
	,D2.Birthdate AS Blue
FROM OrdDates D1
	JOIN OrdDates D2
		ON D2.rn = D1.rn + 1
ORDER BY Yellow DESC;

bretedward
Starting Member

7 Posts

Posted - 2014-08-11 : 13:37:29

The goal is to have 1 column with all dates (without duplicates and in descending order) except the earliest date and the other column with all dates (without duplicates and in descending order) except the latest date.

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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-12 : 08:01:29

OK I get it now. Using my temp tables above, this does the trick:


with 
a as (
	select BirthDate d from #citizens
    union
	select DeathDate d from #citizens
	where DeathDate is not null
	),

b as (
	select d, rn = ROW_NUMBER() over( order by d)
	from a
	)

--SELECT * FROM B

select b.d Yellow, b1.d Blue from b
join b b1
  on b1.rn = b.rn-1
order by b.d desc, b1.d desc

yields:


Yellow	Blue
2013-11-23	2013-11-19
2013-11-19	2012-10-01
2012-10-01	2010-10-08
2010-10-08	2007-12-14

Going forward, when you need help, be sure to post:

1. CREATE TABLE statements to define tables for the query.
2. INSERT INTO statements to populate the tables with test date.
3. Your query so far -- make sure that it parses correctly without syntax errors! (yours had many)
4. The result you are getting
5. The results you want, along with a clear explanation of the business rules to be followed.

bretedward
Starting Member

7 Posts

Posted - 2014-08-18 : 01:46:08

Thank you for all of your hard work on this.

Is there a way to this in MS Access?

I keyed in what you last gave me and the following error occurs: cannot find object rn = ROW_NUMBER() over( order by d)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources