filtering rows from mysql column if it does not exist in other table query-CodePudding

I have two tables and i want to get the result if it exists in tableA only for example you can see that in tableA 081 ID does not exist but in tableB column it exist so if we do this query

 tableA table



 ID    SID         actions
   08     60420       082
   08     60420       083
   08     60420       084

but in my query for above output is

   SELECT * FROM tableB where SID='60420' and PID='08' order by id desc limit 10

 tableB 


  ID      SID      PID
  081     60420      08 
  082     60420      08 
  083     60420      08 
  084     60420      08

PID of tableB and ID of tableA are same as you can see in output. SID is also same for particular customer how can i ensure that it removes 081 rows from the output as it is not existing in tableA or is it possible i delete 081 rows from tableB so that my query still works without any issue i tried join but as SID is not unique it results in many rows as output should i try distinct ?? i did not try distinct as it may have some performance issues any help will be great

CodePudding user response：

You want rows from TableB but only where there exists a correlation, so use exists

select * 
from tableB b
where b.SID='60420' and b.PID='08' 
    and exists (select * from tableA a where a.actions=b.ID and a.SID=b.SID)
order by b.id desc 
limit 10;

CodePudding user response：

You can simply join two tables with inner join. In MySQL inner join works with keyword JOIN. It will exclude rows which don't exist in other table or in other words, will show only rows which exist in both tables:

SELECT tableB.*
FROM tableB 
JOIN tableA
  ON tableA.actions = tableB.ID
    AND tableA.SID = tableB.SID
    AND tableA.ID = tableB.PID
WHERE SID='60420'
  AND PID='08'
ORDER BY tableB.ID desc
LIMIT 10