SQL searching logic on multiple fields, not quite right.

[Fangbeast]

Much thanks AJM, that seems to work. Now to fine tune some of the conditional search fields. I have to decide what columns are relevant for searches and add them in and do more testing.

[percy_b]

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

Sorry, Fangbeast, I just saw your message; haven't been on the Purebasic forum too much these days.

But, it looks like "Ajm" correctly addressed your question.

To make things just a little more optimal, we can remove one set of parenthesis like this:

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

In the scenario above, as stated by "Ajm", you want to treat the OR conditions as a block since only one of them needs to be True.

[Ajm]

Sorry I misunderstood what you was asking. I thought you only wanted to include the AND set in the statement were the first set of OR were true.

[Fangbeast]

Sorry I misunderstood what you was asking. I thought you only wanted to include the AND set in the statement were the first set of OR were true.

Ah, no, it was stated badly by me (normal!). The or block was as percy_b stated, needed with the AND block. Both yours and percy_b's solutions worked great.

I know about lack of time. So much property maintenance has to be done by me because my wife injured her foot so I have to do more. Hopefully won't fall off the roof tomorrow as I still need to code sometime:):)

[percy_b]

I know about lack of time. So much property maintenance has to be done by me because my wife injured her foot so I have to do more. Hopefully won't fall off the roof tomorrow as I still need to code sometime:):)

Sorry about the wife's foot injury. Just remember to wear your parachute while on the roof, LOL!

[Fangbeast]

I know about lack of time. So much property maintenance has to be done by me because my wife injured her foot so I have to do more. Hopefully won't fall off the roof tomorrow as I still need to code sometime:):)

Sorry about the wife's foot injury. Just remember to wear your parachute while on the roof, LOL!

A pair of the old English bloomers strapped to my back perhaps??

[Fangbeast]

Damn, I didn't fall, Oh well, it's only the first spray, the the topcoat later, there is still time to dive over the edge, trying out the bloomers strapped to my back