Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2012
    Posts
    7

    Unanswered: Search query - multiple tables + IDs and keywords

    Hi all,

    I am trying to make a search query that will find multiple keywords in multiple tables. The main table has 2 IDs that point to the same table.

    I want my keyword search to be in the form of 'AND' where all keywords must be found in the record.

    Here is an example of the DB structure:

    files
    ----
    ID
    Name
    Date
    agent1ID
    agent2ID
    clientID

    clients
    ------
    ID
    Name
    Address
    Phone

    agents
    ------
    ID
    Name
    Address
    Phone

    notes
    -----
    ID
    file_ID
    Date
    Body

    A file has 2 agents and 1 client. A file can have many notes.

    Here is what I have so far:

    SELECT * FROM files AS f
    LEFT JOIN agents AS a ON (f.agent1ID = a.ID)
    LEFT JOIN agents AS a2 ON (f.agent2ID = a2.ID)
    LEFT JOIN clients AS c ON (f.clientID = c.ID)
    LEFT JOIN notes AS n ON (f.ID = n.file_ID)
    WHERE (f.Name like '%keyword1%' OR f.Date like '%keyword1%' OR a.Name like '%keyword1%' OR a.Address like '%keyword1%' OR a.Phone like '%keyword1%' OR a2.Name like '%keyword1%' OR a2.Address like '%keyword1%' OR a2.Phone like '%keyword1%' OR c.Name like '%keyword1%' OR c.Address like '%keyword1%' OR c.Phone like '%keyword1%' OR n.Date like '%keyword1%' OR n.Body like '%keyword1%') AND (f.Name like '%keyword2%' OR f.Date like '%keyword2%' OR a.Name like '%keyword2%' OR a.Address like '%keyword2%' OR a.Phone like '%keyword2%' OR a2.Name like '%keyword2%' OR a2.Address like '%keyword2%' OR a2.Phone like '%keyword2%' OR c.Name like '%keyword2%' OR c.Address like '%keyword2%' OR c.Phone like '%keyword2%' OR n.Date like '%keyword2%' OR n.Body like '%keyword2%') GROUP BY f.ID ORDER BY f.Date ASC LIMIT 50, 50;

    The WHERE () clauses are generated by a loop through a list of the desired fields for each keyword. Perhaps there is a much simpler way of doing this kind of thing - if so, please let me know!!

    Unfortunately, it does not seem to return expected results. It works for the most part, but with certain combinations of keywords (all of which are in a file and it's associated records) it returns no results.

    Any ideas? Anyone having done this before should be able to shed some light on this for me. Any help is greatly appreciated! Many thanks in advance!

    I can provide some sample data and the results of the test queries if needed.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's one idea (doubt it's anywhere near the best option though)
    Code:
    WHERE  field1 + field2 + field3 + ... + fieldN LIKE '%keyword1%'
    AND    field1 + field2 + field3 + ... + fieldN LIKE '%keyword2%'
    AND    ...
    AND    field1 + field2 + field3 + ... + fieldN LIKE '%keywordN%'
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2012
    Posts
    84
    Quote Originally Posted by integris View Post
    I can provide some sample data and the results of the test queries if needed.
    It would be helpful.

  4. #4
    Join Date
    Feb 2012
    Posts
    7
    I think I know why it does not work sometimes. The problem is the many notes for 1 file part. I ran the SELECT query on my database in myPhpAdmin and it returns a row for each note with all of the info from the other tables in each row which is normal. So the SELECT is working as expected. My logic is not!

    The problem comes in when I search for a keyword found in 1 note and a different keyword found in a different note (both notes associated to the same file). The 2 keywords are not found in 1 individual row so no results are returned so I will need some different logic for the searching part.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by integris View Post
    The problem comes in when I search for a keyword found in 1 note and a different keyword found in a different note (both notes associated to the same file). The 2 keywords are not found in 1 individual row so no results are returned so I will need some different logic for the searching part.
    i can help you fix this part

    however, i am quite lost as to how you would want to incorporate this into your search query

    i see you doing a GROUP BY and i cannot see why you're doing that

    finding the 2 keywords in 2 different rows in the notes table will require a GROUP BY, but then the question is, do you need to return just the fact that they were found (yes/no), or do you want to see all the details of both notes, or do you want to see all notes including the 2 that were found

    this has to be straightened out before you begin to write the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2012
    Posts
    84
    Create table 'keywords', fill it with desired search pattern,
    and try below query. This query retrieves id from file table
    which match all keywords in 'kewords' table.
    Code:
    create table keywords(
      keyword varchar(50) primary key
    );
    insert into keywords values( '%key1%');
    insert into keywords values( '%key2%');
    insert into keywords values( '%key3%');
    insert into keywords values( '%key4%');
    
    select id, count( keyword )
    from (
        select f.id, k.keyword 
        from files f
        join clients c on f.clientID = c.id
        join keywords k
        on ( c.name like k.keyword or c.address like k.keyword or c.phone like k.keyword )
        union
        select n.file_id, k1.keyword 
        from notes n
        join ( select * from keywords ) k1
        on ( n.body like k1.keyword  )
        union
        select f.id, k.keyword 
        from files f
        join agents a
        on ( f.agent1id = a.id  or f.agent2id = a.id)
        join keywords k 
        on ( a.name like k.keyword or a.address like k.keyword or a.phone like k.keyword )
        union
        select f.id, k.keyword
        from files f
        join keywords k
        on f.name like k.keyword
    ) x
    group by id
    having count( keyword ) = ( select count(*) from keywords )

  7. #7
    Join Date
    Feb 2012
    Posts
    7
    kordirko: wow! that looks interesting. I can certainly give that a shot.

    r937: all I want to do is is return the files where the keywords were found. I do not need to access the information from the notes themselves. The notes contents are displayed when someone clicks on the file.

    Thank you to everyone so far for all the help. I think I will be able to accomplish this with the help everyone is providing!

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, once you've finished testing kordirko's suggestion, give me a shout and i'll walk you through the subquery for 2 keywords in 2 different notes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2012
    Posts
    7
    r937: actually, I do not really like kordirko's solution because multiple people can be searching the system at the same time. so then I would have to manage the keywords along with their session id as to not get the keywords mixed up between users.

    I would rather a straight SQL query that would do the job without having to add another table to my structure if possible.

    Thanks a lot!

  10. #10
    Join Date
    Feb 2012
    Posts
    7
    I have implemented kordirko's solution and it seems to work perfectly!

    However, I have not yet implemented the session_ids in the keywords table to make sure searches don't get mixed up between users.

    I like the fact that the SQL statement does not have to be manipulated directly with the keywords. The statement stays the same for any keyword search.

    One thing I forgot to mention in my structure below is that a note also has an agentID. Different agents can post notes on a file and when a user clicks on the file, they can see the agent's info for each note. I would also like the query to search their Name, Address and Phone for each note.

    Thanks a lot for all your help, guys. I would have never figured out a query like that. I am OK in basic SQL but do not have the experience to take on a query like this.

  11. #11
    Join Date
    Feb 2012
    Posts
    7
    OK, so I got everything working! (to also search the agent info that wrote the note) and to support multiple simultaneous users.

    For all those who are curious, here is the final query.

    You have to have a session_id field in your keywords table to keep track of which user searched for what. That field needs to be populated with the session ID of the logged in user when you insert your keywords into the table.

    Don't forget to flush the keywords out of the table after the search is done, otherwise you will end up with a VERY full table after a while!!

    Code:
    select id, count( keyword )
    from (
        select f.id, k.keyword 
        from files f
        join clients c on f.clientID = c.id
        join ( select * from keywords where session_id = '" . session_id() . "') k
        on ( c.name like k.keyword or c.address like k.keyword or c.phone like k.keyword )
        union
        select n.file_id, k1.keyword 
        from notes n
        join ( select * from keywords where session_id = '" . session_id() . "') k1
        on ( n.body like k1.keyword  )
        union
        select n2.ID, k2.keyword
        from notes n2
        join agents a2 on n2.agent_ID = a2.ID
        join ( select * from keywords where session_id = '" . session_id() . "') k2
        on ( a2.Name like k2.keyword or a2.Address like k2.keyword )
        union
        select f.id, k.keyword 
        from files f
        join agents a
        on ( f.agent1id = a.id  or f.agent2id = a.id)
        join ( select * from keywords where session_id = '" . session_id() . "') k
        on ( a.name like k.keyword or a.address like k.keyword or a.phone like k.keyword )
        union
        select f.id, k.keyword
        from files f
        join ( select * from keywords where session_id = '" . session_id() . "') k
        on f.name like k.keyword
    ) x
    group by id
    having count( keyword ) = ( select count(*) from keywords where session_id = '" . session_id() . "')
    Enjoy!

    Note that my 'notes' table had the extra agent_ID field compared to my original post as so:

    notes
    -----
    ID
    file_ID
    agent_ID
    Date
    Body

  12. #12
    Join Date
    Feb 2012
    Posts
    7
    Oh yeah!

    Thanks a MILLION to everyone for helping! I would never have dreamed this solution up in a thousand years! Without you, I would never have got this working. Keep up the great help!

Posting Permissions

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