May 6, 2002 at 7:03 pm
Hi, I am using sql server2000.
I have a problem to use intersect when I am converting some oracle db code to sql server.
my table is like this:
create table my_intersect (
user_id numeric(8,0),
thing_id int )
my table values are like this:
my_intersect
-----------------------------------
1, 1
1, 2
1, 3
2, 2
2, 3
3, 1
3, 4
4, 1
4, 2
....
my original oracle code is:
select user_id
from my_intersect
where thing_id = 1
intersect
select user_id
from my_intersect
where thing_id = 2
intersect
select user_id
from my_intersect
where thing_id = 4
Now, in sql server 2000, when I wrote this:
(select user_id
from my_intersect
where thing_id = 1)
intersect
(select user_id
from my_intersect
where thing_id = 2)
it complaints incorrect syntaxt near keyword
intersect.
I checked the help menu in sql server, looks like the intersect has to be used in this way: intersect (set1, set2 )
but I need to intersect more than 2 sets, also, my sql is a dynamic sql, based on the thing_id search criteria.
How should I construct the dynamic sql to intersect sets?
Any kind of idea is welcome. Thanks in advance.
Abby
May 6, 2002 at 7:17 pm
My current way to do it is using this sql:
select user_id
from my_intersect
where user_id in
(select user_id
from my_intersect
where thing_id = 1
)
and user_id in
(select user_id
from my_intersect
where thing_id = 2
)
and user_id in
(select user_id
from my_intersect
where thing_id = 3
)
group by user_id
But I am wondering if there is any much more efficient way to do it?
Thanks.
Abby
May 7, 2002 at 6:15 am
This should be the same as what you are doing now.
SELECT
user_id
FROM
my_intersect
WHERE
thing_id IN (1, 2, 3)
GROUP BY user_id
Not sure you need the group by unless you have a grouping need but I left may use distinct keyword instead. Which would look like this.
SELECT DISTINCT
user_id
FROM
my_intersect
WHERE
thing_id IN (1, 2, 3)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 7, 2002 at 11:04 am
Hi, Thanks for the reply. The problem is:
when use
IN (1, 2, 3 ), you are using 'OR' logic.
NOT the same purpose for 'intersect'.
It should be an 'AND' logic, which means,
the qualified user_id should have an entry in
1, AND an entry in 2, AND an entry in 3,
the logic is not the same as IN (1, 2, 3).
The reason I use group here, is the same as 'distint', just don't know which will be faster.
Abby
May 7, 2002 at 11:39 am
Ahhh...
So try this
SELECT
user_id
FROM
my_intersect
WHERE
user_id IN (SELECT user_id FROM my_intersect1 WHERE thing_id = 1) AND
user_id IN (SELECT user_id FROM my_intersect1 WHERE thing_id = 2) AND
`user_id IN (SELECT user_id FROM my_intersect1 WHERE thing_id = 3)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 7, 2002 at 3:36 pm
I see, it's the SAME as I posted above ( see the second post for this question). Not sure if this is the most efficient way to resolve this kind of logic issue.
So, basically, we don't have something in sqlserver 2000 as 'intersect' which is equivalent to oracle's 'intersect'. Is that true?
Abby
May 8, 2002 at 5:37 am
We do but for Analysis Services at this point not SQL itself. You may consider posting to MS as a feature request for future versions. But as for now the way you did the second time (which for some reason I missed) is the only direct way I know of.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 10, 2002 at 11:56 am
Does anyone have a reason why MS has not allowed Intersect, Minus queries even when they are allowed by most common databases. If such queries are allowed then many of the prograaming logics would fit in better and faster.
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
May 11, 2002 at 7:20 pm
This query can be used if you plan to use INTERSECT
USE pubs
GO
SELECT pub_name
FROM publishers
WHERE NOT EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = 'business')
ORDER BY pub_name
GO
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
May 11, 2002 at 7:33 pm
The only problem with Exists is the way exists works and the fact he wanted to may sure exists with each possible non just any one possibility. The reason MS has not in most cases adopted items is just a choice not to and if you want to see place a product improvement with them. However most items in SQL come from MS trying to do there own thing and the SQL ANSI 92 in the current and apparently the 99 standards will be brought into swing with the next release.
This is the page you can suggest improvements to a product, and if enough people request they do tend to make them.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply