Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2012
    Posts
    2

    Recommendations fro search

    Hey all.

    I am looking for some sort of a database that can do a specific to general search.

    So, for example, I want to do a search where:
    Company=MegaCorp OR Division=SuperDivision OR Employee=Rob

    I want this to return a record for 'Rob'. If that record doesn't exist, I want it to return the record for 'SuperDivision'. If that record doesn't exist, I want the query to return the record for 'MegaCorp'.

    Does that make sense?

    Thanks!
    -Rob

  2. #2
    Join Date
    Jul 2012
    Posts
    2
    Also, say I had a record where
    Company=MegaCorp, Division=MegaSquad, Employee=Rob

    Then the above search would return only the value for MegaCorp...


    This is almost like a file system with index.html files, except that it returns the most specific HTML file it can find.

  3. #3
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Possibly you want:
    Company=MegaCorp AND Division=SuperDivision AND Employee=Rob
    OR
    Company=MegaCorp AND Division=SuperDivision
    OR
    Company=MegaCorp

    There could be employees named Rob many places and i suspect you want the one that is within the proper company and division.

    Suggest you provide more details as to what you want to accomplish other than the the initial post.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    virtually any database can do that, but you have to design it and the user interface to to meet that and any other requirements you may have
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want this to return a record for 'Rob'.
    If that record doesn't exist, I want it to return the record for 'SuperDivision'.
    If that record doesn't exist, I want the query to return the record for 'MegaCorp'.
    Please try...
    Code:
    ...
    ...
     WHERE Employee = 'Rob'
       OR  NOT EXISTS
           (SELECT 0
             FROM  table_a
             WHERE Employee = 'Rob'
           )
       AND Division = 'SuperDivision'
       OR  NOT EXISTS
           (SELECT 0
             FROM  table_a
             WHERE Employee = 'Rob'
               OR  Division = 'SuperDivision'
           )
       AND Company = 'MegaCorp'
    ;
    If your RDBMS supports RANK function, this also might work...
    Code:
    SELECT Company , Division , Employee
     FROM (SELECT t.*
                , RANK()
                     OVER( ORDER BY
                                 CASE
                                 WHEN Employee = 'Rob'           THEN 0
                                 WHEN Division = 'SuperDivision' THEN 1
                                 WHEN Company  = 'MegaCorp'      THEN 2
                                 ELSE                                 9
                                 END
                         ) rank
            FROM  table_a AS t
          )
     WHERE rank = 1
       AND
      (    Company  = 'MegaCorp'
       OR  Division = 'SuperDivision'
       OR  Employee = 'Rob'
      )
    ;

Posting Permissions

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