Mysql – OR Operation in JOINS SQL(MYSQL)

MySQL

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.

Best Answer

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])

Related Solutions

Mysql – Optimizing multi-table left joins

PLEASE use SHOW CREATE TABLE; DESCRIBE is not descriptive enough.

You seem to be working with IPv4 IP addresses. IPv6 is upon us!

Your query inherently must do a full table scan in order to get the "total" upon which you do the ORDER BY. Since there are a million rows, it will take time.

count('*')

The quotes are not needed.

The two SELECTs are likely to be identical; order across JOIN does not matter. It does matter across LEFT JOIN. (INNER and OUTER add nothing.)

Suggestion...

First, see if this gives the correct information (albeit incomplet info):

SELECT  e.sid, e.cid, COUNT(*) AS total
FROM  event AS e
JOIN  iphdr as h ON e.sid = h.sid
                AND  e.cid = h.cid
WHERE  e.is_deleted = 0
GROUP BY  i.ip_src, i.ip_dst
ORDER BY  total DESC
LIMIT  5;

If so, then use that as a subquery, a la FROM (that-select) and LEFT JOIN to the other two tables to get the rest of the info.

The trick... The subquery is doing less work since it is touching 2 tables (vs 4). The LEFT JOINs only have to do 5 probes each, not a million.

MySQL LEFT OUTER JOIN – How to Use Within Reason

First consider a query that computes which rows are actually relevant from tablethree. With the assumption that with "most recently entered result" you mean "most recent enddate" the following query would gather the appropriate rows:

SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid

Now you can build a join to retrieve not only sid, but all of the data of tablethree:

SELECT a.*
FROM tablethree a
INNER JOIN (
  SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) b
ON a.sid = b.sid AND a.enddate = b.enddate

This is the result set you actually want to "left join in". You have to insert this into your original query:

SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
  ON t1.cid = t2.id
LEFT OUTER JOIN (
  SELECT a.*
  FROM tablethree a
  INNER JOIN (
    SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
  ) b
  ON a.sid = b.sid AND a.enddate = b.enddate
) AS t3
  ON t3.sid = t2.sid
WHERE t1.fieldone = 1 
  AND t1.odate NOT BETWEEN t3.startdate AND t3.enddate

What should also work is the following:

SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
  ON t1.cid = t2.id
LEFT OUTER JOIN tablethree AS t3
  ON t3.sid = t2.sid
LEFT OUTER JOIN (
  SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) mostrecent
  ON t3.sid = mostrecent.sid AND t3.enddate = mostrecent.enddate

WHERE t1.fieldone = 1 
  AND t1.odate NOT BETWEEN t3.startdate AND t3.enddate
  AND mostrecent.enddate IS NULL

This includes both tablethree and the new SELECT as left joins, and sorts out the rows where mostrecent.enddate IS NULL (meaning those rows which are actually not most recent). This should lead to the same result, but MySQL may be able to compute this result a little faster. EXPLAIN on both queries should reveal possible differences in computation.

Best Answer

Related Solutions

Mysql – Optimizing multi-table left joins

MySQL LEFT OUTER JOIN – How to Use Within Reason

Related Question