CODEWITHSUNDEEP
mysqlsql
DCJones
DCJones

Reputation: 3451

Mysql Exclude records from LEFT OUTER JOIN

I am trying to write a query that returns all the records from table 1 and all the records from table2 where there is a match BUT exclude records from both tables where a column contains a set content.

SELECT h.UniqueID, h.Room, h.AuditBy, h.AuditDate, h.SeqID, h.Stat, h.RepairCode, d.SeqNo, d.SeqHeader, d.SeqText 
FROM  NXLHR_SequenceNo_default d 
LEFT OUTER JOIN  NXLHR_Hist_17 h 
ON d.SeqID = h.SeqID AND h.UniqueID = 'NXLHR02571493893502' 
WHERE h.Stat = 1 OR h.Stat = 2 AND RepairCode IS NULL AND h.SeqID != '1306' OR h.SeqID != '1307'

At the moment my query retruns records where the column h.SeqID contains '1306' OR h.SeqID contains '1307'

Is there a way to filter out any records where the column SeqID contains either '1306' or '1307'from the result.

My current sample data enter image description here

Many thanks in advance for your time.

Upvotes: 0

Views: 68

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

From what you describe, you seem to want an inner join. You have a problem in the WHERE clause with parentheses around the conditions -- but this is more easily fixed using IN and NOT IN:

SELECT h.UniqueID, h.Room, h.AuditBy, h.AuditDate, h.SeqID, h.Stat,
       h.RepairCode, d.SeqNo, d.SeqHeader, d.SeqText 
FROM  NXLHR_SequenceNo_default d INNER JOIN
      NXLHR_Hist_17 h 
      ON d.SeqID = h.SeqID AND h.UniqueID = 'NXLHR02571493893502' 
WHERE h.Stat IN (1, 2) AND
      h.RepairCode IS NULL AND
      h.SeqID NOT IN ('SeqID1306', 'SeqID1307') ;

Upvotes: 1

Related Questions