SQL Query Syntax Help - INTERSECT

[jerald717]

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?

[Muddy_Funster]

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.

[jerald717]

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.

[jerald717]

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?

[Muddy_Funster]

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)

[jerald717]

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.

[jerald717]

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

[Muddy_Funster]

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.

[fenway]

Better to JOIN once per answer you're looking for.

[Muddy_Funster]

fenway could you please elaborate on that?

[fenway]

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

[Muddy_Funster]

nice one, thanks for that fenway

[fenway]

nice one, thanks for that fenway

Not a problem -- that's much more efficient, especially when there are very few that only have 2.