CODEWITHSUNDEEP
mysqlsql
Jon
Jon

Reputation: 113

SQL INTERSECT query

I want to display table with the relevant columns. For instance, there are 3 tables (department, dept_emp and employees). If I am interested in only dept_no, my query should return department and dept_emp tables. However, if I am interested in dept_no and emp_no, the result should be only dept_emp table.

So I came up with this query, but apparently there is a syntax error. May I know if there are any other alternatives?

(SELECT TABLE_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE COLUMN_NAME = 'dept_no')
INTERSECT
(SELECT TABLE_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE COLUMN_NAME = 'empt_no')

Upvotes: 2

Views: 140

Answers (2)

Uriil
Uriil

Reputation: 12618

As long as you using MySql, you can do it via subquery:

    SELECT DISTINCT TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'dept_no' AND TABLE_NAME IN (SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'empt_no')

Upvotes: 2

t-clausen.dk
t-clausen.dk

Reputation: 44316

Try:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
 WHERE COLUMN_NAME = 'dept_no'
INTERSECT
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'empt_no'

Upvotes: 1

Related Questions