kajalg
asked on
MS SQL query
Hi all,
I want to perform an intersection or minus operation in Ms SQL.
I was trying the following:
(select language.language from language)
intersect
(select language.language
from language,user,user_languag
where
language.id = user_language.user_id
and
user.id = user_language.lang_id)
The table structures are:
user:
id varchar primary key
name varchar
language:
id varchar primary key
language varchar
user_language:
user_id varchar
lang_id varchar
It's working fine if I give replace intersect with union. But for intersect it's giving the following error:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'intersect'.
But I need to do the intersect operation. Does MS SQL not support intersect or my query is wrong.
Pls help
Thanks in advance
I want to perform an intersection or minus operation in Ms SQL.
I was trying the following:
(select language.language from language)
intersect
(select language.language
from language,user,user_languag
where
language.id = user_language.user_id
and
user.id = user_language.lang_id)
The table structures are:
user:
id varchar primary key
name varchar
language:
id varchar primary key
language varchar
user_language:
user_id varchar
lang_id varchar
It's working fine if I give replace intersect with union. But for intersect it's giving the following error:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'intersect'.
But I need to do the intersect operation. Does MS SQL not support intersect or my query is wrong.
Pls help
Thanks in advance
you don't need the INTERSECT in the above query, because the inner joins checks for existence of the language in the language table
HTH
Hilaire
HTH
Hilaire
select distinct l.language
from language l inner user_language ul on l.id = ul.language_id
inner join user u on u.id = ul.user_id
from language l inner user_language ul on l.id = ul.language_id
inner join user u on u.id = ul.user_id
ASKER
There was an error in the query I gave. The where clause should be
"........
language.id = user_language.lang_id
and
user.id = user_language.user_id....
"
I think you pointed out the mistake.
However I run your query.
select distinct l.language
from language l inner user_language ul on l.id = ul.language_id
inner join user u on u.id = ul.user_id
It's giving the following error
Server: Msg 155, Level 15, State 1, Line 2
'user_language' is not a recognized join option.
"........
language.id = user_language.lang_id
and
user.id = user_language.user_id....
"
I think you pointed out the mistake.
However I run your query.
select distinct l.language
from language l inner user_language ul on l.id = ul.language_id
inner join user u on u.id = ul.user_id
It's giving the following error
Server: Msg 155, Level 15, State 1, Line 2
'user_language' is not a recognized join option.
ASKER
Actually what rowset I need is the language name some user doesn't have.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You should get what you want with
select distinct language.language
from language l inner user_language ul on l.id = ul.language_id
inner join user u on u.id = ul.user_id