jerald717 Posted February 23, 2011 Share Posted February 23, 2011 So apparently this doesn't work: SELECT tols.tutor_id FROM tutor_overall_level_subject AS tols WHERE tols.subject_level_id LIKE 1 INTERSECT SELECT tols.tutor_id FROM tutor_overall_level_subject AS tols WHERE tols.subject_level_id LIKE 2 MySQL said: Documentation #1064 - 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 'INTERSECT SELECT tols.tutor_id FROM tutor_overall_level_subject AS tols WHERE t' at line 4 tried looking and googling all over but can't seem to find anything wrong with it. Help? Link to comment https://forums.phpfreaks.com/topic/228592-sql-query-syntax-help-intersect/ Share on other sites More sharing options...
Muddy_Funster Posted February 23, 2011 Share Posted February 23, 2011 Probably it's because MySQL doesn't actualy support the INTERSECT command. I'm not sure what DB app you learned that in, but for MySQL you're going to have to learn how to use the JOIN command. Link to comment https://forums.phpfreaks.com/topic/228592-sql-query-syntax-help-intersect/#findComment-1178625 Share on other sites More sharing options...
jerald717 Posted February 23, 2011 Author Share Posted February 23, 2011 ah, the JOIN command? alright i'll check it out. thanks. basically, i'm trying to obtain an intersection of those 2 select queries, so that i wont have any "repeated" tutor_ids. Link to comment https://forums.phpfreaks.com/topic/228592-sql-query-syntax-help-intersect/#findComment-1178627 Share on other sites More sharing options...
jerald717 Posted February 23, 2011 Author Share Posted February 23, 2011 Hmm I dont think the JOIN will work as well as I'd want it to work though. I tried: SELECT tols.tutor_id FROM tutor_overall_level_subject AS tols JOIN tutor_profile AS tp ON tols.tutor_id = tp.tutor_id WHERE tols.subject_level_id LIKE 1 AND tols.subject_level_id LIKE 2 and this returns an empty set. doesnt work the way intersect is supposed to work. I have 2 tables, tutor_overall_level_subject AS tols and tutor_profile AS tp, with tutor_id as key. in tols, one tutor_id can have many different subject_level_ids. I want to be able to query SQL such that I pull out tutors that have selected say subject levels 1 AND 2 (tutors who have selected only subject levels 1 or subject level 2 will not show up). anyone has ideas? Link to comment https://forums.phpfreaks.com/topic/228592-sql-query-syntax-help-intersect/#findComment-1178632 Share on other sites More sharing options...
Muddy_Funster Posted February 23, 2011 Share Posted February 23, 2011 sounds like your using the wrong kind of join, see if this helps: SELECT tols.tutor_id FROM tutor_overall_level_subject AS tols LEFT JOIN tutor_profile AS tp ON (tols.tutor_id = tp.tutor_id) (WHERE tols.subject_level_id) = 1 AND (tols.subject_level_id = 2) Link to comment https://forums.phpfreaks.com/topic/228592-sql-query-syntax-help-intersect/#findComment-1178642 Share on other sites More sharing options...
jerald717 Posted February 23, 2011 Author Share Posted February 23, 2011 LEFT JOIN gave an empty set still. SELECT tols.tutor_id FROM tutor_overall_level_subject AS tols LEFT JOIN tutor_profile AS tp ON (tols.tutor_id = tp.tutor_id) WHERE (tols.subject_level_id = 1) AND (tols.subject_level_id = 2) I think it's got to do with WHERE (tols.subject_level_id = 1) AND (tols.subject_level_id = 2) which returns the null set? The problem is for each subject_level_id , it's stored in a different row. So there could be 2 entries for tutor A in tols. tutor A - subject 1 , tutor A - subject 2. Link to comment https://forums.phpfreaks.com/topic/228592-sql-query-syntax-help-intersect/#findComment-1178646 Share on other sites More sharing options...
jerald717 Posted February 23, 2011 Author Share Posted February 23, 2011 here's the EXPLAIN output for the abovementioned query, if it helps. id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tols ALL NULL NULL NULL NULL 49990 Using where 1 SIMPLE tp ALL NULL NULL NULL NULL 16 Link to comment https://forums.phpfreaks.com/topic/228592-sql-query-syntax-help-intersect/#findComment-1178650 Share on other sites More sharing options...
Muddy_Funster Posted February 23, 2011 Share Posted February 23, 2011 Yeah, your right, it can't ever equal both 1 and 2 at the same time. A work around mat be: SELECT tols.tutor_id FROM tutor_overall_level_subject AS tols INNER JOIN tutor_profile AS tp ON (tols.tutor_id = tp.tutor_id) having COUNT (tols.subject_level_id) = 2 This should show only those that have taken 2 cources. Not too flexible, but I'll keep looking. Link to comment https://forums.phpfreaks.com/topic/228592-sql-query-syntax-help-intersect/#findComment-1178671 Share on other sites More sharing options...
fenway Posted February 24, 2011 Share Posted February 24, 2011 Better to JOIN once per answer you're looking for. Link to comment https://forums.phpfreaks.com/topic/228592-sql-query-syntax-help-intersect/#findComment-1179100 Share on other sites More sharing options...
Muddy_Funster Posted February 24, 2011 Share Posted February 24, 2011 fenway could you please elaborate on that? Link to comment https://forums.phpfreaks.com/topic/228592-sql-query-syntax-help-intersect/#findComment-1179292 Share on other sites More sharing options...
fenway Posted February 25, 2011 Share Posted February 25, 2011 SELECT tp.tutor_id FROM tutor_profile AS tp CROSS JOIN tutor_overall_level_subject AS tols1 (tols1.tutor_id = tp.tutor_id) CROSS JOIN tutor_overall_level_subject AS tols2 (tols2.tutor_id = tp.tutor_id) WHERE tols1.subject_level_id = 1 AND tols2.subject_level_id = 2 Link to comment https://forums.phpfreaks.com/topic/228592-sql-query-syntax-help-intersect/#findComment-1179363 Share on other sites More sharing options...
Muddy_Funster Posted February 25, 2011 Share Posted February 25, 2011 nice one, thanks for that fenway Link to comment https://forums.phpfreaks.com/topic/228592-sql-query-syntax-help-intersect/#findComment-1179453 Share on other sites More sharing options...
fenway Posted February 25, 2011 Share Posted February 25, 2011 nice one, thanks for that fenway Not a problem -- that's much more efficient, especially when there are very few that only have 2. Link to comment https://forums.phpfreaks.com/topic/228592-sql-query-syntax-help-intersect/#findComment-1179522 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.