topic Unexplainable SQL Result in Discussions

Unexplainable SQL Result

peterrow — Sat, 10 Jun 2023 23:58:52 GMT

I know! SQL can be strange and throw up peculiar results at times, but this is really simple SQL with an allegedly simple result.

What I have is 3 tables.

Table 1: One column, which is a list of dates covering every day between 31.12.2019 and 31.12.2023.

Table 2: Two columns: Column 1 is the date of an event and column 2 is the count of the number of times the event occurred on the date in column 1.

Table 3: Two columns: Column 1 is the date of a different event and column 2 is the count of the number of times the event occurred on the date in column 1.

I want to combine the three tables into a three-column table (date, occurrences of event 1, occurrences of event 2).

SQL (it doesn't get much simpler)

SELECT t1.Date, t2."N Rows", t3."N Rows" AS "N Rows 2" 
FROM Date_to_2024  t1 
	LEFT OUTER JOIN QNs_Opened t2 
		ON  ( t1.Date = t2."Created On" )  
	LEFT OUTER JOIN QNs_Closed t3 
		ON  ( t1.Date = t3."Completn date" )

Table 1: (Plenty of Data occurring after 10/08/2021)

Table 2: (Plenty of Data occurring after 10/08/2021)

Query Result

Data from table 1 ("Created on", "N Rows 2") - Correct.

Data from table 2 ("Completn Date", "N Rows") - Correct until 10/08/2021, then no further data.

I've tried switching table 1 and table 2. I've tried importing the data again and re-running the query. I have recreated the query from scratch...no luck.

Any ideas?

Re: Unexplainable SQL Result

peterrow — Thu, 22 Dec 2022 10:21:27 GMT

Incidentally, I have solved the problem by using the "Join Table" function (Join two of the tables and then join the third to the result). That worked.

I'd like to know what is wrong with the SQL though!

Re: Unexplainable SQL Result

Jed_Campbell — Thu, 22 Dec 2022 16:48:02 GMT

Weird. If I run your exact query, I get what you're expecting. What version of JMP are you on?

Re: Unexplainable SQL Result

jthi — Thu, 22 Dec 2022 17:28:21 GMT

@Jed_Campbell you have to go more down, it is 10th of August 2021 when the issue occurs (or after it)

I also tried checking with jmps datetime values and still the same issue

QNs_Closed.jmp has one empty row, after removing it the query started working properly. No idea why.

You can see this happening in the preview, create query with the row still there, build query, open review, delete the row and update preview

Re: Unexplainable SQL Result

Jed_Campbell — Thu, 22 Dec 2022 17:50:03 GMT

Well that is curious (and I duplicated it). I wonder why Aug 2021 is the date things go astray.

Re: Unexplainable SQL Result

peterrow — Fri, 23 Dec 2022 07:50:25 GMT

Thanks for the help. The empty line is expected in the data, but I could delete it with code before running the query. I'm not sure that should be necessary though.

In the future I'll keep an eye out for that but for now, two good and easy workarounds,