SQL Query Problem

[mleefiend]

I have this SQL query that should be simple, but it doesn't seem to return any queries. I know there are records that have the word "amalgam" in there, but this query won't return any results. Any help would be appreciated. This query is for a search function that searchs for a keyword within Notes and Procedures and returns Category ID, etc.

SELECT DISTINCT tbl_Category.Category_ID, tbl_Category.Description, tbl_Category.Display_Order
FROM tbl_Category, tbl_Subcategory, tbl_ProcedureGroup, tbl_Procedure, tbl_Note, tbl_ProcedureNote
WHERE tbl_Category.Category_ID = tbl_Subcategory.Category_ID
AND tbl_Subcategory.Subcategory_ID = tbl_ProcedureGroup.Subcategory_ID
AND tbl_ProcedureGroup.Procedure_Group_ID = tbl_Procedure.Procedure_Group_ID
AND tbl_Procedure.Procedure_ID = tbl_ProcedureNote.Procedure_ID
AND tbl_ProcedureNote.Note_ID = tbl_Note.Note_ID
AND (tbl_Procedure.Description LIKE '%amalgam%' OR tbl_Note.Note LIKE '%amalgam%')
ORDER BY tbl_Category.Display_Order

If I rewrite the query like the following below, then it works, but I need it to search two tables, not just one:

SELECT DISTINCT tbl_Category.Category_ID, tbl_Category.Description, tbl_Category.Display_Order
FROM tbl_Category, tbl_Subcategory, tbl_ProcedureGroup, tbl_Procedure, tbl_Note, tbl_ProcedureNote
WHERE tbl_Category.Category_ID = tbl_Subcategory.Category_ID
AND tbl_Subcategory.Subcategory_ID = tbl_ProcedureGroup.Subcategory_ID
AND tbl_ProcedureGroup.Procedure_Group_ID = tbl_Procedure.Procedure_Group_ID
AND (tbl_Procedure.Description LIKE '%amalgam%')
ORDER BY tbl_Category.Display_Order

 

[tlbroadbent]

The table relationships make this query difficult. It appears that you'll either need to use LEFT JOINs at some point or try a different query. Here is one suggestion. I can't test it but hope you can work with it.

SELECT DISTINCT
c.Category_ID,
c.Description,
c.Display_Order
FROM tbl_Category c
INNER JOIN tbl_Subcategory sc
ON c.Category_ID = sc.Category_ID
INNER JOIN tbl_ProcedureGroup pg
ON sc.Subcategory_ID = pg.Subcategory_ID

WHERE EXISTS
(SELECT *
FROM tbl_Procedure p1
INNER JOIN tbl_ProcedureNote pn
ON p1.Procedure_ID = pn.Procedure_ID
WHERE p1.Procedure_Group_ID = pg.Procedure_Group_ID
AND p1.Description LIKE '%amalgam%')

OR EXISTS
(SELECT *
FROM tbl_Procedure p2
INNER JOIN tbl_ProcedureNote pn
ON p2.Procedure_ID = pn.Procedure_ID
INNER JOIN tbl_Note n
ON n.Note_ID = pn.Note_ID
WHERE p2.Procedure_Group_ID = pg.Procedure_Group_ID
AND n.Note LIKE '%amalgam%')

ORDER BY c.Display_Order Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com