Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2009
    Posts
    50

    Unanswered: Join on 3 tables with wild card for Search

    I have 3 tables related by errirID. Errors, soluitions, and alt(solutions).
    here is the lay out of the tables

    Errors
    ErrorId
    ErrorName
    ErrorRank

    Solutions
    SolutionID
    errorId
    Rank
    Summary
    Detail
    Type

    Alt
    AltID
    errorId
    Rank
    Summary
    Detail
    Type

    I need to search the summary and detail fields of the Solutions and Alt table and return the ErrorId and the ErrorName from the errors table for each record in the Solutions and alt table that contains the wildcard search string.

    any help is very much appreciated
    thanks in advance
    Jim
    PS: Im using VS C# 2008 express edition
    Last edited by jbedson; 12-26-09 at 07:40.

  2. #2
    Join Date
    Jun 2008
    Location
    Chennai
    Posts
    21
    Select ErrorId, ErrorName

    From Errors E

    Inner Join Solutions S
    On E.ErrorId = S.ErrorId

    Inner Join Alt A
    On S.ErrorId = A.ErrorId

    Where S.Detail = A.Detail

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT 'solution'   AS rowtype
         , s.SolutionID AS id
         , s.Rank
         , s.Summary
         , s.Detail
         , s.Type
      FROM Solutions AS s
    INNER
      JOIN Errors AS e
        ON e.ErrorId = s.errorId
     WHERE s.Summary LIKE '%searchstring%'
        OR s.Detail  LIKE '%searchstring%'
    UNION ALL
    SELECT 'alt' 
         , a.AltID
         , a.Rank
         , a.Summary
         , a.Detail
         , a.Type
      FROM Alt AS a
    INNER
      JOIN Errors AS e
        ON e.ErrorId = a.errorId
     WHERE a.Summary LIKE '%searchstring%'
        OR a.Detail  LIKE '%searchstring%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2009
    Posts
    50
    Thanks for the help, I'll try to incorporate these into what I'm doing. But they don't get me what I'm looking for.

    Here are some more details:

    I'm attempting to add a search function to my program.
    I need to search the Summary and Detail fields of the "Solutions" and "Alt" tables for a word or phrase that the user would like to find. I need to use the results from that search to populate a drop down list. The drop down list is used to display all the summary's from each table that are related to the errorId. Here is what I've been trying to do so far:

    Select Error.ErrorID, Error.ErrorName
    from Errors, Solutions, Alt
    where Errors.ErrorID = Solutions.ErrorID
    and Errors.ErrorID = Alt.ErrorID
    and Solutions.Summary Like '*variable*'
    or Solutions.Detail Like '*variable*'
    or Alt.Summary Like '*variable*'
    or Alt.Detail Like '*variable*'

    This is not working. Sorry if I wasn't more clear and thanks for your responses.
    Jim

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rewrite your query using JOIN syntax and *poof* it will work correctly

    guaranteed SQL magic



    p.s. ANSI SQL (the forum you're in) uses % as the wildcard character, you must be using ms access, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2009
    Posts
    50
    yes, i'm using ms access... for now

    this is what I pieced together:

    Code:
    Select e.ErrorID, e.ErrorName
    from Errors as e
    inner join solutions as s
    on e.errorid = s.errorid
    where s.summary like '*@term*'
    or s.detail like '*@term*'
    union all
    Select e.errorid, e.errorname
    from errors as e
    inner join alt as a
    on  e.errorid = a.errorid
    where a.summary like '*@term*'
    or a.detail like '*@term*'
    this is working when I put a string where @term is. How can I make it work with @term as a variable?

    P.S. it does not return anything from my C# code. Is there a C# group here?

    and thank you everyone for the help

  7. #7
    Join Date
    Dec 2009
    Posts
    50
    I got most of the bugs worked out and it's working like it should

    Code:
    Select e.ErrorID, e.ErrorName
    from Errors as e
      inner join solutions as s
      on e.errorid = s.errorid
         where s.summary like "*" & [term] & "*"
         or s.detail like "*" & [term] & "*"
    UNION ALL 
    Select e.errorid, e.errorname
    from errors as e
      inner join alt as a
      on  e.errorid = a.errorid
        where a.summary like "*" & [term] & "*"
        or a.detail like "*" & [term] & "*";
    I have one last question: if an errrorid is in the solutions table and in the alts table, i get 2 records in the results table. I would like to get DISTINCT results but I'm not sure where or how to add this. Any sugestions?

    Thanks again for the guidence.
    Jim

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jbedson View Post
    I would like to get DISTINCT results but I'm not sure where or how to add this. Any sugestions?
    yes, change UNION ALL to UNION

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

Posting Permissions

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