Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query generating numerous duplicate results

Status
Not open for further replies.
deepatpaul

deepatpaul

Programmer
Jul 7, 2004
57
US
Any help on why this query is retrieving the same record multiple times would be greatly appreciated:

SELECT p.projectID, p.projectTitle, p.isNamePrivate
FROM tblProjects p, tblWorkshops w
WHERE p.healthName = 'Tuberculosis'
OR (p.healthName = 'Respiratory Diseases'
AND p.stepsTaken LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Respiratory Diseases'
AND p.primaryObjective LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Respiratory Diseases'
AND p.primaryResults LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Respiratory Diseases'
AND p.secondaryObjective LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Respiratory Diseases'
AND p.secondaryResults LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Respiratory Diseases'
AND p.challenges LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Respiratory Diseases'
AND p.q1What LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Respiratory Diseases'
AND p.q2Whom LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Respiratory Diseases'
AND p.q3Where LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Respiratory Diseases'
AND p.q4Length LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Respiratory Diseases'
AND p.q5Objective LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Respiratory Diseases'
AND p.q6Measure LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Communicable (Infectious) Diseases'
AND p.stepsTaken LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Communicable (Infectious) Diseases'
AND p.primaryObjective LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Communicable (Infectious) Diseases'
AND p.primaryResults LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Communicable (Infectious) Diseases'
AND p.secondaryObjective LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Communicable (Infectious) Diseases'
AND p.secondaryResults LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Communicable (Infectious) Diseases'
AND p.challenges LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Communicable (Infectious) Diseases'
AND p.q1What LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Communicable (Infectious) Diseases'
AND p.q2Whom LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Communicable (Infectious) Diseases'
AND p.q3Where LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Communicable (Infectious) Diseases'
AND p.q4Length LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Communicable (Infectious) Diseases'
AND p.q5Objective LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
OR (p.healthName = 'Communicable (Infectious) Diseases'
AND p.q6Measure LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
AND projectID IS NOT NULL
AND p.workshop_ID = w.workshopID
ORDER BY p.country,
w.workshopCity,
substring(w.workshopStart,7,4) DESC,
substring(w.workshopStart,4,2) DESC,
substring(w.workshopStart,1,2) DESC,
p.projectTitle
 
Sort by date Sort by votes
All your OR sub-clauses...

I think you were on the right but the or statements allow for any sort of combination of results... try this...

Consider that when a value evaluates to true it signifies 1... when false it signifies 0..

You're saying..

Select ... FROM ... WHERE 1 OR 1 OR 1 OR 1 OR 1...OR 1 AND 1 AND 1.

The script doesn't know what to do... With my modifications below... it SHOULD say...

Select ... FROM ... WHERE (1 OR 1 OR 1 OR 1 OR 1...OR 1) AND 1 AND 1. which translates to..

Select ... FROM ... WHERE (1) AND 1 AND 1

Code: 
SELECT p.projectID, p.projectTitle, p.isNamePrivate
            FROM tblProjects p, tblWorkshops w
            WHERE [red][b]([/b][/red][gray]p.healthName = 'Tuberculosis'
                OR (p.healthName = 'Respiratory Diseases' 
                    AND p.stepsTaken LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Respiratory Diseases' 
                    AND p.primaryObjective LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Respiratory Diseases' 
                    AND p.primaryResults LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Respiratory Diseases' 
                    AND p.secondaryObjective LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Respiratory Diseases' 
                    AND p.secondaryResults LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Respiratory Diseases' 
                    AND p.challenges LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Respiratory Diseases' 
                    AND p.q1What LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Respiratory Diseases' 
                    AND p.q2Whom LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Respiratory Diseases' 
                    AND p.q3Where LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Respiratory Diseases' 
                    AND p.q4Length LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Respiratory Diseases' 
                    AND p.q5Objective LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Respiratory Diseases' 
                    AND p.q6Measure LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Communicable (Infectious) Diseases' 
                    AND p.stepsTaken LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Communicable (Infectious) Diseases' 
                    AND p.primaryObjective LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Communicable (Infectious) Diseases' 
                    AND p.primaryResults LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Communicable (Infectious) Diseases' 
                    AND p.secondaryObjective LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Communicable (Infectious) Diseases' 
                    AND p.secondaryResults LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Communicable (Infectious) Diseases' 
                    AND p.challenges LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Communicable (Infectious) Diseases' 
                    AND p.q1What LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Communicable (Infectious) Diseases' 
                    AND p.q2Whom LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Communicable (Infectious) Diseases' 
                    AND p.q3Where LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Communicable (Infectious) Diseases' 
                    AND p.q4Length LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Communicable (Infectious) Diseases' 
                    AND p.q5Objective LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)
                OR (p.healthName = 'Communicable (Infectious) Diseases' 
                    AND p.q6Measure LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.keywords#" null="No" />)[/gray][red][b])[/b][/red]
                AND projectID IS NOT NULL
                AND p.workshop_ID = w.workshopID
                ORDER BY p.country,
                        w.workshopCity,
                        substring(w.workshopStart,7,4) DESC,
                        substring(w.workshopStart,4,2) DESC,
                        substring(w.workshopStart,1,2) DESC,
                        p.projectTitle

ALFII.com
---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.
 
Upvote 0 Downvote
What you say makes sense, but each of my OR statements has a sub AND, which should throw a 0 since not all of those comparisons will return true. Are you saying that they all return a true regardless of whether each OR's (var1 = var2 AND var3 = var4) evaluate to true?
 
Upvote 0 Downvote
Figured you weigh in rudy, glad you did too.

Yeah... like I said, that query was really close, but the parentheses should nail it.

Or if not, I'd like to know what the new results do.

The irony is that the other query may not have been working at all, just returning almost any result because of all the ORs.

But yeah, hope it worked.

ALFII.com
---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

fdgsogc
  • Locked
  • Question
Displaying results of an SQL Pivot Query
Replies
1
Views
655
jdhilljr
jdhilljr
Ceilicat
  • Locked
  • Question
Query of Queries Union Not Totaling Across Fields
Replies
2
Views
355
OneTrickPony
OneTrickPony
malibu65k
  • Locked
  • Question
Show a button on a page based on query results
Replies
1
Views
220
okiiyama
okiiyama
redoakhg
  • Locked
  • Question
SQL Query Help 1
Replies
5
Views
316
redoakhg
redoakhg
swansong77
  • Locked
  • Question
Output query and alert if there is a duplicate between 2 tables
Replies
3
Views
252
r937
r937

Part and Inventory Search

Sponsor

Back
Top