[SOLVED] a SQL query question

Basher52 · 05-16-2015, 09:03 PM

This might be the wrong place to ask but I have no other ideas.

I know SQL a lot but I just can't get this thing with mySQL(MariaDB) to work,
probably since my knowledge about the syntax ain't at 100%.
My problem is to get a functional MariaDB-query with date fields.
I also have a isdiabled-field but that is no problem.

The fields are a fromDate and a toDate and are both nullable.
The problem is how I handle if these date-fields is 'null', not 'null' or some of it.

Think of it like this;
Both these dates can be 'null' and if so that the row should be gathered.
If there ain't a start date then only the end date would be stopping the query
and vice versa, all towards current date.
(NOT TIME!!! just the date)

If this was in MSSQL I would fix it, no problem, but I do not know all the syntax of MariaDB
to get this to work. I've googled a lot but probably not using the correct words for that either.

Please help an old man :P

New2Linux2 · 05-16-2015, 10:04 PM

Hello Basher52,

As far as I know, MariaDB takes the same SQL commands that MSSQL, MySQL or any other SQL database uses. It's all SQL.

You might get a more intelligent response if you post the SQL command that you're having trouble with. Reading over your post, I'm not following what exactly it is that you are trying to do with the data in the database. Just query it or massage it a little? Are you wanting to use functions that are specific to MariaDB and not standard SQL commands? Help us out with a bit of detail and perhaps a code snippet.

jlinkels · 05-16-2015, 10:09 PM

I don't understand your condition "all towards the current date". But other than that, I think you should go into this direction:

Code:

SELECT foo FROM bar WHERE (NOT ISNULL(fromDate) and NOT ISNULL(toDate) AND mydate >= fromDate AND mydate <= toDate) OR
(ISNULL fromDate AND NOT ISNULL (toDate) AND mydate <= toDate) OR
(NOT ISNULL fromDate and ISNULL toDate and mydate >= fromDate);

jlinkels

Pearlseattle · 05-17-2015, 10:58 AM

Quote:

Originally Posted by New2Linux2

Hello Basher52,

As far as I know, MariaDB takes the same SQL commands that MSSQL, MySQL or any other SQL database uses. It's all SQL.

Unluckily it's definitely not like that - in reality each engine needs often different commands/syntax and performs differently especially with date fields.

jlinkels' answer is an alternative.

Another one would be to use a "case"-condition., which is like an if-else (or if, else-if, else-if, ...., else) and which I know from Oracle but I did not use yet with MariaDB. The advantage here is that they're easy to read (if you don't overdo it

)

Please keep in mind that at least with MariaDB & its main InnoDB engine, declaring a field as not-"NOT NULL" will impact a lot performance (HDD I/O).

New2Linux2 · 05-17-2015, 11:07 AM

Quote:

Originally Posted by Pearlseattle

Unluckily it's definitely not like that

Thanks for the clarification. I was under the impression that SQL was the standard language/command subset used by all SQL databases, and the differences came into play with their own unique way of doing things, like PostgreSQL's use of slash commands. I appreciate the correction to an erroneous assumption.

Pearlseattle · 05-17-2015, 11:28 AM

Quote:

Originally Posted by New2Linux2

Thanks for the clarification. I was under the impression that SQL was the standard language/command subset used by all SQL databases, and the differences came into play with their own unique way of doing things, like PostgreSQL's use of slash commands. I appreciate the correction to an erroneous assumption.

Eeehhhfffpppp..., you're welcome - you're actually like me 5 years ago

Unluckily it's messy - on one side there are different versions of the SQL-standard, on the other side the DB-producers create own additions and/or variations to commands.

I have some experience with sqlite/Oracle/mysql/mariadb/db2/postgresql (no mssql

) and for me the absolute nightmare is always to extract & import data between these engines => would be great to have a common channel!!

Another area which is really wild is the one that involves "hints". There everybody does whatever it wants and it varies between extremes (e.g. postgresql being the most "pure" and oracle the dirtiest but as well being the most loved in this category).

Table creation SQLs vary as well a lot because of all the possible options (where mariadb is probably the most extreme as its options vary depending on the storage engine that will be used).

Summarized:
doesn't matter what you're programming: when writing your code always try to abstract the commands/functions from what is really used against the DB.

Basher52 · 05-17-2015, 04:01 PM

Thanks all, nice little discussion here too and I agree with Pearlseattle, all DBs I've been in contact with are always different if someways.
jlinkels, I definitely will try that and maybe add some to it, thx
and Pearlseattle, I'll see if I can use that case-condition too if I must.

thx again