Join on 3 tables with wild card for Search

jbedson · #1 (**permalink**) 12-26-09, 05:58

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

kandrusatish · #2 (**permalink**) 12-26-09, 10:10

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

r937 · #3 (**permalink**) 12-26-09, 11:09

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%'

jbedson · #4 (**permalink**) 12-26-09, 20:19

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

r937 · #5 (**permalink**) 12-27-09, 00:39

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

jbedson · #6 (**permalink**) 12-27-09, 02:36

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

jbedson · #7 (**permalink**) 12-27-09, 06:26

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