Checking if an id exists in another table

Question

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.

do you really need `a . * , ap . * , i . * , appstatus . *` all columns from these tables — M Khalid Junaid, May 08 '15 at 19:19

score 2 · Accepted Answer · edited May 23 '17 at 12:14

2

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

edited May 23 '17 at 12:14

Community

1
1

answered May 08 '15 at 19:20

Alex

16,739
1
28
51

I'm just curious why the change to the joins? Is it faster than using the normal AND's? – ComputerLocus May 08 '15 at 19:22
Normal ANDs??? :-) I just have no words :-) I didn't change any `AND` to `JOIN`. check updated answer with some explanation then. – Alex May 08 '15 at 19:29
I am getting the following SQL error with this query: `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN ( SELECT applyid, SUM(rating) / ' at line 8`. There was a comma after `r.count` which I don't think should be there so I removed that. And is the line `SUM(IF(rating > 0,1,0) as count` supposed to have another bracket at the end of the rating > 0,1,0) part? – ComputerLocus May 08 '15 at 19:34
my bad. check the extra comma after `r.count` should be removed. Yes. both notes are correct. Fixed my answer – Alex May 08 '15 at 19:41
and by the way I am not sure about `ON a.applicantid = at.applicantid`. probably it must be `ON a.applyid = at.applyid` – Alex May 08 '15 at 19:43
Yes it should be the applyid version, the columns are quite confusing but I guess sometimes you gotta deal with what you're given :/. I am still getting that same SQL error as I commented about before. – ComputerLocus May 08 '15 at 19:46
oh... I seee... remove another comma after `FROM apply AS a` – Alex May 08 '15 at 19:48
I'm testing out implementing the changes and seeing what kind of speed improvement there is. I'll get back to you – ComputerLocus May 08 '15 at 19:58
Looks like it is definitely faster. Thanks – ComputerLocus May 08 '15 at 20:25

score 1 · Answer 2 · answered May 08 '15 at 19:05

1

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

answered May 08 '15 at 19:05

Mark Leiber

3,118
2
13
22

Thanks, that looks to have worked for me! I also had tried to do it with the method in the brackets but I was off by two words in that version. Looks like I kept getting close but not close enough. – ComputerLocus May 08 '15 at 19:07

Checking if an id exists in another table

2 Answers2