Sql: Syntax Error with Intersect

SQL: Syntax error with intersect?

MySQL, which you appear to be using, does not support the INTERSECT syntax. You're going to have to solve it another way.

In this case, it is trivial -we only need a list of all suppliers that offer "green" and "red" of some part- your query does not bother to see if the parts themselves are related, so we can solve it quite easily like this:

SELECT Suppliers.sid
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid
WHERE Parts.color IN ('red', 'green')
GROUP BY Suppliers.sid
HAVING COUNT(DISTINCT Parts.color) = 2

Personally, I don't believe the original query is a typical INTERSECT problem. Take a look at the JOIN solution offered by Vinko Vrsalovic for a general solution to emulate the INTERSECT (which I would btw prefer even if the RDBMS would in fact offer INTERSECT natively).

SQL Syntax error when using INTERSECT in UPDATE query

INTERSECT doesn't have any meaning in terms of updates. Your SELECT query is actually five independent queries being run simultaneously in order to return the intersection of the five independent result sets. Since UPDATE doesn't generate a result set, it's an error to try to intersect its non-result with the results of SELECT queries.

It does seem like your original purpose would be better served with a single SELECT query with a complex WHERE clause, since each of the five queries is running against the same table checking different conditions.

Syntax error near INTERSECT

ORDER BY is your culprit.

It is not valid on subqueries/derived tables (http://msdn.microsoft.com/en-us/library/ms188385(v=sql.105).aspx). Meaning there should only be 1 ORDER BY clause at the end of the query.

Remove all but the last ORDER BY and you should be good.

select SOURCE_SYS_CD,COUNT(*) as [COUNT BY APP] from STAGING_TRANSACTIONS
GROUP BY SOURCE_SYS_CD
INTERSECT 
select SOURCE_SYS_CD,COUNT(*) as [COUNT BY APP] from TMS..TB_TRANSACTIONS
GROUP BY SOURCE_SYS_CD
ORDER BY SOURCE_SYS_CD

I cannot use the INTERSECT operator

Query the list of CITY names from STATION which have vowels as both their first and last characters.

You seem to be overcomplicating this. As I understand the question, you just need a where clause that filters the table once - regexes come handy for this:

select city
from station
where city regexp '^[aeiou].*[aeiou]$'

The pattern describes a string that starts and ends with a wovel (^ represents the beginning of the string, and $ is the end).

If you have duplicate city in the table, then use select distinct city from ... instead.

If you want to use left() and right():

select city
from station
where 
    right(city, 1) in ('a', 'e', 'i', 'o', 'u')
    and left(city, 1) in ('a', 'e', 'i', 'o', 'u')

Getting ER_PARSE_ERROR when using Intersect and multiple input vaules of email format

there is not INTERSECT in mysql ..

if you really need an intersect you could use several inner join

select student_email 
from register r
INNER JOIN  (
  select student_email from 
  register where teacher_email=? and valid=1 
) t1 ON t1.student_email = r.student_email
INNER JOIN  (
  INTERSECT 
  INTERSECT select student_email from register 
  where teacher_email=? and valid=1
) t2 ON t2.student_email = t1.student_email 
where r.teacher_email=? and r.valid=1 

---

Related Topics