Results 1 to 13 of 13
  1. #1
    Join Date
    May 2004
    Posts
    5

    Unanswered: Query across all colums

    Hi folks. This is my first post around here.
    So I want to query a select on a table. Is there a simpler way to match the where clause with all columns than referencing every single column in the where clause?

    Thanks

    Shabassa

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not really
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Posts
    5
    Ok, thanks was worth a question.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you are comparing all the columns in one table to all the columns in another, you may be able to use the CHECKSUM or BINARYCHECKSUM functions:

    select subA.APKey, sbuB.BPKey
    from
    (select A.PKey as APKey, CHECKSUM(*) as ChecksumA from A) subA
    full outer join
    (select B.PKey as BPKey, CHECKSUM(*) as ChecksumB from B) subB
    on subA.ChecksumA = subB.ChecksumB
    where ....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    blindman, i think the problem was to write this --

    ... WHERE a LIKE '%x%' OR b LIKE '%x%' OR c LIKE '%x%' OR d LIKE '%x%'

    in some easier fashion, e.g.

    ... WHERE allcolumns LIKE '%x%'

    and the answer, of course, is "not really"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    While Rudy is quite right in terms of how SQL itself does things, there are extensions to many database engines that make this kind of search easier. In MS-SQL, this is called Full Text Indexing.

    -PatP

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat, all things considered, full text indexing is not "simpler" (one of shabassa's original requirements)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    From the standpoint of managing the server, you are quite right. Full-Text Indexing brings its own problems to the table.

    From the standpoint of writing a search query though, I feel that it is simpler and I don't think anyone would argue it is much less code.

    -PatP

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by r937
    blindman, i think the problem was to write this --

    ... WHERE a LIKE '%x%' OR b LIKE '%x%' OR c LIKE '%x%' OR d LIKE '%x%'

    in some easier fashion, e.g.

    ... WHERE allcolumns LIKE '%x%'

    and the answer, of course, is "not really"
    Could be. Without a doubt he needs to be more specific.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I doubt it though.....

    I think they're looking for duplicates...

    Or they're just trying to establish a join between to related tables, just in the keys?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Or attempting to create a search feature for their application that searches against a catalog of objects where the entry could match a value in any number of fields (Ex: Name, Description, Price, etc).
    That which does not kill me postpones the inevitable.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Step right up, folks! Play "Guess the user requirements" and win a prize for the little lady! Who's next? Every player has a chance to win! You sir, yes you...!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    May 2004
    Posts
    5

    Red face

    Well if I was programming an application that searched across the table there would be no problem doing it in some kind of loop. I was just lazy and wanted to look for a certain row where I entered a date in an application accessing that db. But I didn't know to which colum the text field in the app corresponded to. So I wanted to search across all columns at once using the Enterprise Manager.
    I hope that clears things up.

    How would this be possible with full text indexing? Do I magically get some kind of search field?

    Shabassa

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •