Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    13

    Post Unanswered: Multiple Attributes search

    Hi there,

    I have a problem and maybe someone here can help me. I have the following situation. I have a joined data-set that has multiple attributes for each entry. Something like:

    DOC_ID | ATTR
    document1 | attribute1
    document1 | attribute2
    document1 | attribute3
    document1 | attribute4
    document2 | attribute1
    document2 | attribute2
    document3 | attribute1
    document3 | attribute2
    document3 | attribute3
    ...

    Now what I want to do is a kind of search. The user should define some attribute out of 5 lists, where they could choose 'don't care' also. This should give all documents, that fulfill all conditions.

    Something like: ATTR like '%Facility%' and ATTR like '%Comment1%' and ...

    AS you can easily see the problem is, that all attributes are in one row... (they are in a separated table).

    DO you see any chance to do this??

    Thank you very much!
    Regards,
    pilzbug

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    since the multiple attributes are on multiple rows, you need to treat the multiple rows for each document as a group
    Code:
    SELECT doc_id
      FROM daTable
     WHERE attr LIKE '%Facility%' 
        OR attr LIKE '%Comment1%'
    GROUP
        BY doc_id
    HAVING COUNT(*) = 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >since the multiple attributes are on multiple rows, you need to treat the multiple rows for each document as a group

    likely return multiple rows most of which are not desrired
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anacedent View Post
    likely return multiple rows most of which are not desrired
    dude, did you even look at my query?

    it returns one row per document, and ~only~ for those documents that meet all the given criteria
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2010
    Posts
    13

    Thx

    Hi,

    thank you very much. This is a really good idea and it works fine.

    What I now want to do is getting it dynamically with user defined parameters. Lets see, what I can do

    Regards,
    pilzbug
    Last edited by pilzbug; 10-05-10 at 03:15.

Posting Permissions

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