CODEWITHSUNDEEP
mysqlsql
ComputerLocus
ComputerLocus

Reputation: 3628

Checking if an id exists in another table

I have a fairly large SQL query right now:

SELECT a . * , ap . * , i . * , appstatus . * ,(SELECT SUM(rating) / count( case when rating > 0 then 1 end ) AS total_rating FROM review AS r WHERE r.applyid=a.applyid GROUP BY applyid) AS total_rating, (SELECT count(rating) FROM review AS r WHERE r.applyid=a.applyid GROUP BY applyid) as count
                FROM apply AS a, applicant AS ap, interest AS i, application_status AS appstatus, apply_tags as at
                WHERE a.applicantid = ap.applicantid
                AND a.applicantid = i.applicantid
                AND a.application_status_id = appstatus.application_status_id
                AND a.archive =  'false'
                AND a.jobid =27

I need to modify this so that another table is also checked to see if the a.applyid exists in it or not.

Based on my existing query I want to modify it so it will only be outputting rows that do not have data in the apply_tags table.

I am not sure the best approach to integrate this into my query. Should I be using a join?

I am essentially looking to add an AND statement like this:

AND a.applyid NOT EXIST at.applyid (at is the apply_tags table)

I know I am doing this wrong, however that is an example of the kind of thing I am trying to do.

Upvotes: 1

Views: 871

Answers (2)

Alex
Alex

Reputation: 17289

Sorry, I see that you already choose one correct answer. But your query is so far from perfect. So here is just an idea for you how your query could be like if you want optimize it one day:

SELECT a . * , 
ap . * , 
i . * , 
appstatus . * ,
r.total_rating, 
r.count
FROM apply AS a
LEFT JOIN (
  SELECT applyid, 
    SUM(rating) / SUM(IF(rating > 0,1,0)) AS total_rating,
    SUM(IF(rating > 0,1,0)) as count
  FROM review  
  GROUP BY applyid) AS r
ON r.applyid=a.applyid
LEFT JOIN applicant AS ap
ON a.applicantid = ap.applicantid
LEFT JOIN interest AS i
ON a.applicantid = i.applicantid
LEFT JOIN application_status AS appstatus
ON a.application_status_id = appstatus.application_status_id
LEFT JOIN apply_tags as at
ON a.applyid = at.applyid
WHERE a.archive =  'false'
   AND a.jobid =27
   AND at.applicantid IS NULL

SMALL EXPLANATION In your original query you do something like:

SELECT a . * , 
...,
(SELECT SUM(rating) / count( case when rating > 0 then 1 end ) AS total_rating FROM review AS r WHERE r.applyid=a.applyid GROUP BY applyid) AS total_rating,
(SELECT count(rating) FROM review AS r WHERE r.applyid=a.applyid GROUP BY applyid) as count
....

You asked what wrong with this technique? So the problem is that MySQL will try to execute both of this 2 SELECT on every record received from a.table. In opposite to my LEFT JOIN that will be requested just one time fro all records. if you have 100 records in a MySQL will call 100x2=200 times your subquery, and only 1 mine one.

Almost the same reason with WHERE a.applicantid = i.applicantid vs LEFT JOIN i ON a.applicantid = i.applicantid. Last one absolutely optimized by MySQL. first one is very slow and heavy. If you ignore features of MySQL it was invented for. You can SELECT * from all 5 tables into php arrays and execute all logic on php side.

You can just google a little. For example: https://stackoverflow.com/a/2242015/4421474

Upvotes: 2

Mark Leiber
Mark Leiber

Reputation: 3138

Your attempt is very close. Just add:

and not exists (select 1 from apply_tags where applyid = a.applyid)

Here is the documentation on using EXISTS/NOT EXISTS:

https://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

Upvotes: 1

Related Questions