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.