Search multiple columns AND extra comparator

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Search multiple columns AND extra comparator

Flakheart
Hello folks, sorry to bother you. You must admit I take 6 months or more to ask questions:):)

Using the first part of this query works fine to return data by searching the multiple concatenated columns, very happy with that.

SELECT * FROM sitelist
 WHERE sitename||username||password||serialnum||misc1||misc2||misc3||misc4||misc5||notes LIKE '%wik%' 7

But I also want to AND the following columns to apply to the results and I am not getting it (So what's new, I hear you say??)

 AND category='Bundled Software'
 AND owner='Gary'

 ORDER BY sitename

So I want the columns to be searched for a match (working) but ONLY DISPLAYED if the category and owner are matching as well (Not working).  Have tried all manner of ANDs and ORs and WHEREes in various ways and am not getting it.

Would anyone be able to elucidate?
Reply | Threaded
Open this post in threaded view
|

Re: Search multiple columns AND extra coparator

Simon Slavin-3

On 25 Apr 2010, at 2:44pm, flakpit wrote:

> Using the first part of this query works fine to return data by searching
> the multiple concatenated columns, very happy with that.
>
> SELECT * FROM sitelist
> WHERE
> sitename||username||password||serialnum||misc1||misc2||misc3||misc4||misc5||notes
> LIKE '%wik%' 7
>
> But I also want to AND the following columns to apply to the results and I
> am not getting it (So what's new, I hear you say??)
>
> AND category='Bundled Software'
> AND owner='Gary'
>
> ORDER BY sitename

Just add your AND and ORDER by clauses on after the '%wik%'.  But I don't know why there's a '7' at the end of your original line.  I don't think it does anything.  Perhaps a typo when you made the post ?

SELECT * FROM sitelist
WHERE sitename||username||password||serialnum||misc1||misc2||misc3||misc4||misc5||notes LIKE '%wik%'
AND category='Bundled Software'
AND owner='Gary'
ORDER BY sitename

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Search multiple columns AND extra coparator

Flakheart
>Just add your AND and ORDER by clauses on after the '%wik%'.  But I don't know why there's a '7' at >the end of your original line.  I don't think it does anything.  Perhaps a typo when you made the post ?

>SELECT * FROM sitelist
>WHERE sitename||username||password||serialnum||misc1||misc2||misc3||misc4||misc5||notes LIKE >'%wik%'
>AND category='Bundled Software'
>AND owner='Gary'
>ORDER BY sitename

Hello Simon, that's what I tried first (yes, the '7' was a typo, sorry) and it didn't work. I know there is a limit to field concatenation this way but I don't know what it is so will have to play as it is not giving me any results at the moment.

Oh well, thanks for the idea...I don't really need any sleep tonight..:):)

*UPDATE* I manage to track it down. it seems with this current lib version (Maybe all for all I know), the current concatenated field limit *seems* to be 4 or at least, it seems so. When I cut down the first row to 4 columns a search of sorts does work.

However, the AND owner='Gary' isn't working, the records are shown regardless of owner.

Back to the drawing board for me.

*Update* This is my full list of fields below (The hard way) but works a treat)

SELECT * FROM sitelist WHERE (sitename LIKE '%wik%' OR
   lsitename  LIKE '%wik%' OR sitename   LIKE '%wik%' OR lusername  LIKE '%wik%' OR username   LIKE '%wik%' OR   lpassword  LIKE '%wik%' OR password   LIKE '%wik%' OR lserialnum LIKE '%wik%' OR serialnum  LIKE '%wik%' OR   lmisc1     LIKE '%wik%' OR misc1      LIKE '%wik%' OR lmisc2     LIKE '%wik%' OR misc2      LIKE '%wik%' OR   lmisc3     LIKE '%wik%' OR misc3      LIKE '%wik%' OR lmisc4     LIKE '%wik%' OR misc4      LIKE '%wik%' OR   lmisc5     LIKE '%wik%' OR misc5      LIKE '%wik%' OR notes      LIKE '%wik%')  
AND owner='Miki' AND category='Site Login' ORDER BY sitename
Reply | Threaded
Open this post in threaded view
|

Re: Search multiple columns AND extra coparator

Igor Tandetnik
flakpit wrote:

>> SELECT * FROM sitelist
>> WHERE
> sitename||username||password||serialnum||misc1||misc2||misc3||misc4||misc5||notes
> LIKE >'%wik%'
>> AND category='Bundled Software'
>> AND owner='Gary'
>> ORDER BY sitename
>
> Hello Simon, that's what I tried first (yes, the '7' was a typo, sorry) and
> it didn't work.

Define "didn't work". What results did this query return, and how do these results differ from your expectations?

> it is not giving me any results at the moment.

Well, do you actually have any records that match all the conditions? Show a sample of your data; in particular, show the record that you believe should be returned by the query.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Search multiple columns AND extra coparator

Flakheart

Igor Tandetnik wrote
Define "didn't work". What results did this query return, and how do these results differ from your expectations?

> it is not giving me any results at the moment.

Well, do you actually have any records that match all the conditions? Show a sample of your data; in particular, show the record that you believe should be returned by the query.
--
Igor Tandetnik
Thanks Igor but as I indicated to Simon, I finally figured it out and pasted the resulting query string to Simon. Now I may fall out of this chair and die quietly.

P.s. Your help in the past has also been invaluable, thank you.