1

I am using MYSQL

SELECT  m.proposalId, m.title, n.stageNumber, n.committeeId, n.modifiedDate,
        o.msNumber , o.description,o.ics,o.edition
    FROM  mystands_Proposal m
    INNER join  mystands_ProjectLifecycle n on m.proposalId = n.proposalId
    INNER join  mystands_Project o          on m.proposalId = o.proposalId
    WHERE  n.newState=0
      AND  n.committeeId=79827
      AND  (n.assignedTo=29913 OR  n.actionBy=29913 )
      AND  n.proposalId LIKE '%sdas%'
      AND  o.projectNumber LIKE '%sdass%'
      AND  n.stageNumber=40.92
      AND  o.category=1
      AND  o.degreeofCorrespondence=1
      AND  o.msNumber LIKE '%sdas%'
      AND  (n.modifiedDate <='2015-05-15' AND  n.stageNumber=40.2 )
      AND  (n.modifiedDate <='2015-05-07' AND  n.stageNumber=30.99 )
      AND  (n.modifiedDate <='2015-05-27' AND  n.stageNumber=55.99 )

I am doing inner joins for three tables for search functionality and in java if the user enters the values then I am appending the value to the sql query on the fly. The above code is working fine for AND operations between fields.

How do I perform "OR" operation for the fields entered by the user and display the result. I have tried this:

SELECT m.proposalId, m.title, n.stageNumber, n.committeeId, n.modifiedDate, o.msNumber , o.description,o.ics,o.edition FROM mystands_Proposal m 
INNER join  
    mystands_ProjectLifecycle n on m.proposalId = n.proposalId 
INNER join
    mystands_Project o on m.proposalId = o.proposalId  
WHERE n.newState=0
      OR  n.committeeId=80246
      OR  (n.assignedTo=79977
              OR  n.actionBy=79977
          )
      OR  n.proposalId LIKE '%ads%'
      OR  o.projectNumber LIKE '%sds%'
      OR  n.stageNumber=30.99
      OR  o.category=1
      OR  o.degreeofCorrespondence=1
      OR  o.msNumber LIKE '%sadsa%'
      OR  (n.modifiedDate <='2015-05-22'
              AND  n.stageNumber=40.2
          )
      OR  (n.modifiedDate <='2015-05-22'
              AND  n.stageNumber=30.99
          )
      OR  (n.modifiedDate <='2015-05-29'
              AND  n.stageNumber=55.99
          )

Now what is happening is it is giving me the results of the inner joins because newState=0 is true and entire where condition is getting true and I am getting the results of inner joins on three table and the result is not being filtered as desired.

Can you please help where am going wrong? Thanks.

  • In this where clause `WHERE n.newState=0 OR n.committeeId=80246 OR (n.assignedTo=79977 OR n.actionBy=79977) OR n.proposalId LIKE '%ads%' OR o.projectNumber LIKE '%sds%' OR n.stageNumber=30.99 OR o.category=1 OR o.degreeofCorrespondence=1 OR o.msNumber LIKE '%sadsa%' OR (n.modifiedDate <='2015-05-22' AND n.stageNumber=40.2) OR (n.modifiedDate <='2015-05-22' AND n.stageNumber=30.99) OR (n.modifiedDate <='2015-05-29' AND n.stageNumber=55.99) ' its appear the result will be always true even if all other conditions are false since the result of OR will be true – Ahmad Abuhasna May 6 '15 at 13:50
  • if at least one of the condition is true – Ahmad Abuhasna May 6 '15 at 13:50
0

If n.newState=0 is always true then just remove it from your where statement.

or if everything you want to return needs to have that true then just change your first OR to a AND. Then it will check to make sure that it is true and one other thing in your where statement is also true.

WHERE n.newState=0 AND ([all your other OR conditions])

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.