RyanSF07 Posted February 16, 2011 Share Posted February 16, 2011 Hello, Is it possible to do this, and if so, could you help me write this correctly? Select video.id, quiz.video_id from video, quiz, WHERE video.id "is NOT found in the quiz.video_id" row. Basically I'd liike to know which "video.ids" are present in the video-table, but not present in the quiz-table. thank you for your help! Ryan Quote Link to comment Share on other sites More sharing options...
kickstart Posted February 16, 2011 Share Posted February 16, 2011 Hi Should be possible using a left outer join. Something like this SELECT video.id, quiz.video_id FROM video LEFT OUTER JOIN quiz ON video.id = quiz.video_id WHERE video.id IS NULL All the best Keith Quote Link to comment Share on other sites More sharing options...
RyanSF07 Posted February 16, 2011 Author Share Posted February 16, 2011 Thank you very much Keith. I googled "left join" and found a situation exactly like my own. The query is the same as yours. When run this though, I get: MySQL returned an empty result set (i.e. zero rows). (Query took 0.0054 sec) Confusing.. The video.id is present in video-table, and non-existent in the quiz-table. Is this query looking for the id, and a value of "unknown"? Or is it Selecting ids that are present in the video-table, but not present in the quiz-table---- or is that the same thing? I appreciate the help and guidance. Thanks again. Ryan Quote Link to comment Share on other sites More sharing options...
RyanSF07 Posted February 16, 2011 Author Share Posted February 16, 2011 I'm also trying "not exist" -- no luck. any other ideas? Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 16, 2011 Share Posted February 16, 2011 I hate to write code for people, but this one is a toughy. Try this (I've been doing MSSQL for months so this may not work the way I expect) SELECT video.id FROM video WHERE video.id NOT IN (SELECT quiz.id FROM quiz) Quote Link to comment Share on other sites More sharing options...
kickstart Posted February 16, 2011 Share Posted February 16, 2011 Or is it Selecting ids that are present in the video-table, but not present in the quiz-table---- or is that the same thing? What it should get is every record from the video table and match that with the matching records from the quiz table. If there were 2 matching records on the quiz table then it would bring back the record from the video table twice, once for each quiz record ; if no matching records then it would bring back the record from the video table with the column from the quiz table being NULL. The WHERE clause will then only return records where the field from the quiz table is NULL, hence only brings back records where there is no matching record on the quiz table. Can you post some sample data? The other suggestion using NOT IN will work but will be far less efficient. All the best Keith Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 16, 2011 Share Posted February 16, 2011 Or is it Selecting ids that are present in the video-table, but not present in the quiz-table---- or is that the same thing? What it should get is every record from the video table and match that with the matching records from the quiz table. If there were 2 matching records on the quiz table then it would bring back the record from the video table twice, once for each quiz record ; if no matching records then it would bring back the record from the video table with the column from the quiz table being NULL. The WHERE clause will then only return records where the field from the quiz table is NULL, hence only brings back records where there is no matching record on the quiz table. Can you post some sample data? The other suggestion using NOT IN will work but will be far less efficient. All the best Keith How is it less efficient? your way sounds like a lot of PHP processing to count how many times each is returned... Quote Link to comment Share on other sites More sharing options...
kickstart Posted February 16, 2011 Share Posted February 16, 2011 Hi MySQL will deal with a subselect like that quite poorly. Quite probably performing the subselect once for each row found on the outer select. All the best Keith Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 16, 2011 Share Posted February 16, 2011 In that case, I'd perform one query to get all the Ids in quiz table, put them in an array, and explode that into your SQL string in the NOT IN (). Quote Link to comment Share on other sites More sharing options...
kickstart Posted February 16, 2011 Share Posted February 16, 2011 Hi That means doing 2 separate queries which is an overhead. The JOIN / NULL method is pretty efficient. All the best Keith Quote Link to comment Share on other sites More sharing options...
RyanSF07 Posted February 17, 2011 Author Share Posted February 17, 2011 Thanks guys. This works for what I needed. And now I'm on to my next speed bump.... Having (count)... which I'll start in a new thread. thank you both so much for your help! I'm learning a lot as I check and google my way through your input. Ryan Quote Link to comment Share on other sites More sharing options...
xylex Posted February 17, 2011 Share Posted February 17, 2011 On the question of efficiency between doing the join and the subquery- most modern SQL parsers are really good at figuring out what you're looking for whether you're selecting from multiple tables, doing a join, or using a subquery. If you run an EXPLAIN for either kickstart or jesirose's query, you'll likely (though not always) see that they are both following the same query plan, making them identical in performance. When the plans do differ, it's usually a toss up between which one is more efficient between the subquery and the join. This didn't always use to be the case- earlier versions of MySQL isolated subqueries and came up with a separate plan, hence the oft-recommended practice of using a join instead of a subquery whenever possible. Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 17, 2011 Share Posted February 17, 2011 On the question of efficiency between doing the join and the subquery- most modern SQL parsers are really good at figuring out what you're looking for whether you're selecting from multiple tables, doing a join, or using a subquery. If you run an EXPLAIN for either kickstart or jesirose's query, you'll likely (though not always) see that they are both following the same query plan, making them identical in performance. When the plans do differ, it's usually a toss up between which one is more efficient between the subquery and the join. This didn't always use to be the case- earlier versions of MySQL isolated subqueries and came up with a separate plan, hence the oft-recommended practice of using a join instead of a subquery whenever possible. Quote Link to comment Share on other sites More sharing options...
kickstart Posted February 17, 2011 Share Posted February 17, 2011 Hi Quick play on my development machine running Mysql 5.1 linking a table with ~970k records with another with ~170k records using a JOIN and a check for null takes just under 4 seconds on average (repeating the SQL several times). Doing the same using NOT IN syntax instead takes a bit over 5.5 seconds on average. In both cases no records were returned (and none should have been, I just used handy tables with lots of records). A smaller difference than I expected to be honest. All the best Keith Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 17, 2011 Share Posted February 17, 2011 Hi Quick play on my development machine running Mysql 5.1 linking a table with ~970k records with another with ~170k records using a JOIN and a check for null takes just under 4 seconds on average (repeating the SQL several times). Doing the same using NOT IN syntax instead takes a bit over 5.5 seconds on average. In both cases no records were returned (and none should have been, I just used handy tables with lots of records). A smaller difference than I expected to be honest. All the best Keith If you haven't tossed the code, can you try it the way I suggested second? If not, I'll probably run a test myself sometime, I'm curious also. This is a neat question. Quote Link to comment Share on other sites More sharing options...
kickstart Posted February 17, 2011 Share Posted February 17, 2011 Hi Afraid with the test data I used that solution would be unworkable (it is something I have used when SQL as otherwise got way too complicated). Don't think Mysql would appreciate 197k elements within an IN clause. Not sure what the limit is within MySQL for elements within an IN clause. Access has a limit of 255 I think (one of many issues with Access, unfortunately I do have to use it quite often), although you can get away with multiple IN clauses and just put an OR between them. All the best Keith Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 17, 2011 Share Posted February 17, 2011 That makes sense - thanks! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.