SQL searching logic on multiple fields, not quite right.

[Fangbeast]

I've been trying a couple of combinations of sql searching logic on multiple fields that I *thought* I had working before but it doesn't so must have been wrong. Tried multiple combinations in a professional sql tool to confirm that I have it wrong.

Tried both combinations below, grouped and ungrouped and I am still getting back all records including the one where deleted = '1' or deleted = 1 and don't know why.

I have checked the deleted field for the one record being returned erroneously (Or goatishly) and the deleted field is definately equal to 1

Could some some sql guru edumfucate me please?

Code: Select all

SELECT *
FROM db_addresses
WHERE (archived = '0' OR favourite = '0' OR locked = '0'   OR deleted = '0')

SELECT *
FROM db_addresses
WHERE archived = '0' OR favourite = '0' OR locked = '0'   OR deleted = '0'

These two *should* have returned all records except the one with deleted = '1' in it. I thought.

[Paul]

Easier to help if you post your database or at least a piece of it that is giving you this problem.

The record where delete=1 ... does archived,favourite or locked=0 ??

Maybe you're wanting to do this instead?

Code: Select all

SELECT *
FROM db_addresses
WHERE archived <> '1' AND favourite <> '1' AND locked <> '1' AND deleted <> '1'

[Fangbeast]

Easier to help if you post your database or at least a piece of it that is giving you this problem.

The record where delete=1 ... does archived,favourite or locked=0 ??

Maybe you're wanting to do this instead?

Code: Select all

SELECT *
FROM db_addresses
WHERE archived <> '1' AND favourite <> '1' AND locked <> '1' AND deleted <> '1'

Thanks Paul but I found out why that condition didn't work and it had to do with the way I was creating the database. One of the table fields was NULL by default on creation and I forget how to prevent that.

Fixed it manually and now the below works.

Code: Select all

SELECT * 
FROM db_addresses 
WHERE 
archived = '0' and
favourite = '0' and
locked = '0' and
deleted = '0'

Was hoping to do this in case I forget to initialise a column with nulls:

Code: Select all

SELECT * 
FROM db_addresses 
WHERE 
archived = '0' or 
archived is null and
favourite = '0' or
favourite is null and
locked = '0' or
locked is null and
deleted = '0' or 
deleted is null

But although it works, my precedence is off because the deleted record shows.

[skywalk]

If your db is SQLite, use DB Browser For SQLite for prototyping SQL queries on the Execute SQL tab. And you can save the queries in a project for later.

[percy_b]

Code: Select all

SELECT * 
FROM db_addresses 
WHERE 
archived = '0' or 
archived is null and
favourite = '0' or
favourite is null and
locked = '0' or
locked is null and
deleted = '0' or 
deleted is null

But although it works, my precedence is off because the deleted record shows.

Hi Fangbeast,

Depending on the type of results you desire from your SQL, you want to be careful about how you combine your ANDs and ORs in your WHERE clause, otherwise your results can be unpredictable. For example, I'm not sure, but I think you're looking to do something similar to the following:

Code: Select all

SELECT *
FROM db_addresses
WHERE
archived = '0' or
( archived is null and
favourite = '0' ) or
( favourite is null and
locked = '0' ) or
( locked is null and
deleted = '0' ) or
deleted is null

Make sure that parenthesis combine the desired logical conditions when you're using multiple OR conditions in conjunction with ANDs. For example, (a AND b) or (c AND d) or (g AND h) as opposed a AND b or c AND d or g AND h.

I hope I didn't make this too convoluted.

[Fangbeast]

I hope I didn't make this too convoluted.

Strangely enough, I followed what you said, even though I would'nt have been able to come up with that myself. Never was logical enough.

Am heavily into a huge project that is sapping my energy and need to learn a few new things in a hurry. (Vicious neighbours are not helping either!!)

Last time I worked on this project was 2007 and I was younger and smarter then (Compared those those pesky goats of srods. I swear he deliberately opens the gates and lets them trample all over the pettunias!!)

[percy_b]

I hope I didn't make this too convoluted.

Strangely enough, I followed what you said, even though I would'nt have been able to come up with that myself. Never was logical enough.

Am heavily into a huge project that is sapping my energy and need to learn a few new things in a hurry. (Vicious neighbours are not helping either!!)

Last time I worked on this project was 2007 and I was younger and smarter then (Compared those those pesky goats of srods. I swear he deliberately opens the gates and lets them trample all over the pettunias!!)

Here's an even better way of performing that query:

Code: Select all

SELECT *
FROM db_addresses
WHERE
isnull(archived, '0') = '0' and
isnull(favourite, '0') = '0' and
isnull(locked, '0') = '0' and
isnull(deleted, '0') = '0'
;

And, yes, I know what it's like to have neighbors from Hell. I had to deal with one for over 10 years.

Sent from my Moto E (4) using Tapatalk

[Fangbeast]

Code: Select all

SELECT *
FROM db_addresses
WHERE
isnull(archived, '0') = '0' and
isnull(favourite, '0') = '0' and
isnull(locked, '0') = '0' and
isnull(deleted, '0') = '0'
;

Weird, I don't understand that but I can sure use it anyway. Thanks for that.

And, yes, I know what it's like to have neighbors from Hell. I had to deal with one for over 10 years.

1 years here. And they now handed us a threatening letter full of lies. All for asking them to keep their monsters quiet. Stress.

Phew, this multi-database program is harder now that I am both older and dumber. But it will be worth not having to manage so many separate programs for myself as I age.

[percy_b]

The ISNULL function allows you to override a NULL value. So, in the example I provided, I was testing for '0'. So, ISNULL(xxxx, '0') allows me to test for '0' and NULL at the same time. The equivalent function in Oracle is NVL(xxx, '0').

This is the same as doing:

WHERE xxx IS NULL OR xxx = '0'


Sent from my Moto E (4) using Tapatalk

[Fangbeast]

Thanks percy_b. Melding 12 database (from all my separate programs) and still adding the little extras each program has is proving to be a nightmare and may keep me busy for a few years but your code (and other sql examples i've been given before) will make it so much easier.

And the statement I made up above "1 years here." was supposed to be '11'. Grrrr

[Fangbeast]

Code: Select all

SELECT *
FROM db_addresses
WHERE
isnull(archived, '0') = '0' and
isnull(favourite, '0') = '0' and
isnull(locked, '0') = '0' and
isnull(deleted, '0') = '0'
;

Weird, I don't understand that but I can sure use it anyway. Thanks for that.

And, yes, I know what it's like to have neighbors from Hell. I had to deal with one for over 10 years.

1 years here. And they now handed us a threatening letter full of lies. All for asking them to keep their monsters quiet. Stress.

Phew, this multi-database program is harder now that I am both older and dumber. But it will be worth not having to manage so many separate programs for myself as I age.

percy_b, I just tried the above with SQLITEEXPERT and it says there is a syntax error near isnull. I don't think sqlite recognises it and I can't find it in sqlite man pages.


NEVER MIND!! It's apparently IFNULL and it works:)

[percy_b]

Hi Fangbeast,

Sorry for the ty-po. I spend much of my time working with larger RDBMS such as PostgreSQL and Oracle and only a small chunk of my time in SQLite. However, I knew that SQLite had that functionality.

I'm glad you were able to figure it out.

[Fangbeast]

Hi Fangbeast,

Sorry for the ty-po. I spend much of my time working with larger RDBMS such as PostgreSQL and Oracle and only a small chunk of my time in SQLite. However, I knew that SQLite had that functionality.

I'm glad you were able to figure it out.

In the grand scale of things that I can't figure out, this is a win. Still wish I could get the hang of modules, this latest project could use them but this will have to do:):):)

[Fangbeast]

percy_b, if you are still around and reading this stuff, could you advise me on the correct order here?

either titlename OR information OR comment should match '%gar%' in any of the three fields then the following 6 fields should be AND into the result.

Instead, the datatype field returns all datatypes and not just 'address'.

I've tried many combinations or brackets around the OR fields and the AND fields but no joy. The last thing I tried resulted in no data returned at all.

Code: Select all

Select * FROM db_keeper 
  WHERE 
  
     titlename LIKE '%gar%' 
  OR information LIKE '%gar%'
  OR comment LIKE '%gar%'
  
  AND owner = 'Gary Farris'
  AND datatype = 'address' 
  AND archived <> '1' 
  AND deleted <> '1' 
  AND favourite <> '1' 
  AND locked <> '1' 
  
  ORDER BY titlename, information 
  ASC LIMIT 50

[Ajm]

Try the following.

Code: Select all

Select * FROM db_keeper 
WHERE 

((titlename LIKE '%gar%' 
OR information LIKE '%gar%'
OR comment LIKE '%gar%')

AND owner = 'Gary Farris'
AND datatype = 'address' 
AND archived <> '1' 
AND deleted <> '1' 
AND favourite <> '1' 
AND locked <> '1')

ORDER BY titlename, information 
ASC LIMIT 50