transactSQL

[tilley]

For some reason INTERSECT doeesnt work in transactSQL but UNION does??!

I can get

(SELECT * FROM table)
UNION
(SELECT * FROM table)

to work but when i change UNION for INTERSECT it gives me 'incorrect syntax
error near INTERSECT keyword'

Anybody got any helpful comments?
Fanx

[Andres Silva]

that's because the Intersect keyword doesn't exist in TransactSQL, you can
try something like an exists condition or a join...you decide:

select * from table1
where exists (select * from table2 where table1.fld1=table2.fld2)

or just

select t1.* from t1 inner join t2 on t1.fld1=t2.fld2

both of them return the same intersection records.

Andres



"tilley" <daniel.tilley@thomastelford.com> wrote:
>
>For some reason INTERSECT doeesnt work in transactSQL but UNION does??!
>
>I can get
>
>(SELECT * FROM table)
>UNION
>(SELECT * FROM table)
>
>to work but when i change UNION for INTERSECT it gives me 'incorrect syntax
>error near INTERSECT keyword'
>
>Anybody got any helpful comments?
>Fanx

[Kevin Downs]

INTERSECT is not supported in T/SQL
If you can give a more concrete example, I may be able to show you how to get
the same result with T/SQL.

--
Regards,
Kevin


"tilley" <daniel.tilley@thomastelford.com> wrote in message
news:3a361f1e$1@news.devx.com...
>
> For some reason INTERSECT doeesnt work in transactSQL but UNION does??!
>
> I can get
>
> (SELECT * FROM table)
> UNION
> (SELECT * FROM table)
>
> to work but when i change UNION for INTERSECT it gives me 'incorrect syntax
> error near INTERSECT keyword'
>
> Anybody got any helpful comments?
> Fanx

[tilley]

Thanks for your help, sorry if this is abit daunting...

Basically i am developing a search page for an SQL database.
There are four search fields, and each has its own SQL Query.
When the results are displayed i just want only results in all four
result sets. ie the INTERSECTION

Also the page is ASP and there are many session variables which
when selected add new WHERE clauses to the SQL statements...

This is the basic database structure:

_________________ __________________
I Organisation I I Services I
----------------- ------------------
I OrgID I I OrgID I
I Name I I TypeID I
----------------- ------------------

_________________________
I OrganisationContacts I
-------------------------
I OrgID I
I LocID I
-------------------------

______________
I Contacts I
--------------
I LocID I
I ZoneID I
--------------

There are loads more fields in the tables but they
are not important to the search...

These are the four BASIC SQL Querys:
(They get longer with the session variables)

SQL 1
----------------------------------------
SELECT ORG.Name
FROM Organisation AS ORG,
Services AS SERV
WHERE ORG.OrgID = SERV.OrgID AND
SERV.TypeID = 1 AND
SERV.TypeID < 0
ORDER BY ORG.Name
----------------------------------------

SQL 2
----------------------------------------
SELECT ORG.Name
FROM Organisation AS ORG,
OrganisationContacts AS ORGCON,
Contacts AS CON
WHERE ORG.OrgID = ORGCON.OrgID AND
ORGCON.LocID = CON.LocID AND
CON.ZoneID = 1 AND
CON.ZoneID < 0
ORDER BY ORG.Name
----------------------------------------

SQL 3
----------------------------------------
SELECT ORG.Name
FROM Organisation AS ORG
WHERE ORG.Name LIKE '%variable%'
ORDER BY ORG.Name
----------------------------------------

SQL 4
----------------------------------------
SELECT ORG.Name
FROM Organisation AS ORG,
Services AS SERV
WHERE ORG.OrgID = SERV.OrgID AND
SERV.TypeID = 1 AND
SERV.TypeID < 0
ORDER BY ORG.Name
----------------------------------------


So as you can see:

(SQL 1)
INTERSECT
(SQL 2)
INTERSECT
(SQL 3)
INTERSECT
(SQL 4)

Would be very EASY and it works with UNION
but alas not INTERSECT...

I need to create the same effect in Transact SQL
So any help at all would be very very much appreciated.

Thankyou in advance

[Michael Levy]

I would think that this would work:

SELECT name
FROM (SQL1)
WHERE name IN (SELECT name FROM SQL2)
AND name IN (SELECT name FROM SQL3)
AND name IN (SELECT name FROM SQL4)

-Mike
--
Michael Levy MCDBA, MCSD, MCT
michaell@gasullivan.com

[Kevin Downs]

(Apology for long post - I haven't included the original as that was even
longer!)

Mmm, tricky...

OK, lets see what we can do - not pretty, but it will work if you are only
returning one column as in your example.

SELECT ORG.Name
FROM
(
(SELECT DISTINCT ORG.Name
blah blah SQL 1)
UNION
(SELECT DISTINCT ORG.Name
blah blah SQL 2)
UNION

(SELECT DISTINCT ORG.Name
blah blah SQL 3)
UNION

(SELECT DISTINCT ORG.Name
blah blah SQL 4)
)
GROUP BY ORG.Name
HAVING COUNT(*) = 4
ORDER BY ORG.Name

If you have to return more than one column from ORG, then it is probably
easiest to use the following

SELECT ORG.* from ORG
INNER JOIN (ABOVE SQL) A
ON ORG.Name = A.Name
ORDER BY ORG.Name

Don't be afraid of nesting SQL <g>

Seriously though, use the Query Analyzer to examine the estimated query plan -
its amazing how well the optimizer works (most of the time)

By the way, I would suggest that you examine and learn the SQL92 join syntax
that TSQL supports. Its more explicit and shows the intent of the SQL more
clearly - especially on more complex multiple joins

For example

SELECT ORG.Name
FROM Organisation AS ORG,
OrganisationContacts AS ORGCON,
Contacts AS CON
WHERE ORG.OrgID = ORGCON.OrgID AND
ORGCON.LocID = CON.LocID AND
CON.ZoneID = 1 AND
CON.ZoneID < 0

becomes

SELECT ORG.Name
FROM Organisation AS ORG,
INNER JOIN OrganisationContacts AS ORGCON
ON ORG.OrgID = ORGCON.OrgID
INNER JOIN Contacts AS CON
ON ORGCON.LocID = CON.LocID
WHERE CON.ZoneID = 1
AND CON.ZoneID < 0

--
Regards,
Kevin

[Kevin Downs]

Much easier than my answer - I shouldn't try to think about these things at
2am...

However it may be a good idea to check the query plan of the two to see which
is more efficient.

--
Regards,
Kevin

"Michael Levy" <michaell@gasullivan.com> wrote in message
news:3a38d07b@news.devx.com...
> I would think that this would work:
>
> SELECT name
> FROM (SQL1)
> WHERE name IN (SELECT name FROM SQL2)
> AND name IN (SELECT name FROM SQL3)
> AND name IN (SELECT name FROM SQL4)
>
> -Mike
> --
> Michael Levy MCDBA, MCSD, MCT
> michaell@gasullivan.com
>
>

[tilley]

Sorry to be a pain in the *** but...

I cant get either of your ideas to work:

IDEA ONE

> SELECT ORG.Name
> FROM
> (
> (SELECT DISTINCT ORG.Name
> blah blah SQL 1)
> UNION
> (SELECT DISTINCT ORG.Name
> blah blah SQL 2)
> UNION
>
> (SELECT DISTINCT ORG.Name
> blah blah SQL 3)
> UNION
>
> (SELECT DISTINCT ORG.Name
> blah blah SQL 4)
> )
> GROUP BY ORG.Name
> HAVING COUNT(*) = 4
> ORDER BY ORG.Name

This type of query works fine:
---------------------------------
SELECT ORG.Name
FROM Organisation AS ORG,
Services AS SERV
WHERE ORG.OrgID = SERV.OrgID AND
SERV.OrgID = 1
GROUP BY ORG.Name
---------------------------------

but as soon as i use nesting it say incorrect syntax near GROUP
---------------------------------
SELECT ORG.Name
FROM
(SELECT DISTINCT ORG.Name
FROM Organisation AS ORG,
Services AS SERV
WHERE ORG.OrgID = SERV.OrgID AND
SERV.OrgID = 1)
GROUP BY ORG.Name
---------------------------------

IDEA 2

> SELECT name
> FROM (SQL1)
> WHERE name IN (SELECT name FROM SQL2)
> AND name IN (SELECT name FROM SQL3)
> AND name IN (SELECT name FROM SQL4)

This gives me an incorrect syntax near WHERE

I dont really under stand why neither of these work?!
Got any other plans?

[hippo]

try it this way (there is an "AS X" clause added and X.Name instead of ORG.Name
in SELECT statement, GROUP and ORDER clause):
> SELECT X.Name
> FROM
> (
> (SELECT DISTINCT ORG.Name
> blah blah SQL 1)
> UNION
> (SELECT DISTINCT ORG.Name
> blah blah SQL 2)
> UNION
>
> (SELECT DISTINCT ORG.Name
> blah blah SQL 3)
> UNION
>
> (SELECT DISTINCT ORG.Name
> blah blah SQL 4)
> ) AS X
> GROUP BY X.Name
> HAVING COUNT(*) = 4
> ORDER BY X.Name

nice day

hippo

"tilley" <daniel.tilley@thomastelford.com> wrote:
>
>Sorry to be a pain in the *** but...
>
>I cant get either of your ideas to work:
>
>IDEA ONE
>
>> SELECT ORG.Name
>> FROM
>> (
>> (SELECT DISTINCT ORG.Name
>> blah blah SQL 1)
>> UNION
>> (SELECT DISTINCT ORG.Name
>> blah blah SQL 2)
>> UNION
>>
>> (SELECT DISTINCT ORG.Name
>> blah blah SQL 3)
>> UNION
>>
>> (SELECT DISTINCT ORG.Name
>> blah blah SQL 4)
>> )
>> GROUP BY ORG.Name
>> HAVING COUNT(*) = 4
>> ORDER BY ORG.Name
>
>This type of query works fine:
>---------------------------------
>SELECT ORG.Name
>FROM Organisation AS ORG,
> Services AS SERV
>WHERE ORG.OrgID = SERV.OrgID AND
> SERV.OrgID = 1
>GROUP BY ORG.Name
>---------------------------------
>
>but as soon as i use nesting it say incorrect syntax near GROUP
>---------------------------------
>SELECT ORG.Name
>FROM
> (SELECT DISTINCT ORG.Name
> FROM Organisation AS ORG,
> Services AS SERV
> WHERE ORG.OrgID = SERV.OrgID AND
> SERV.OrgID = 1)
>GROUP BY ORG.Name
>---------------------------------
>
>IDEA 2
>
>> SELECT name
>> FROM (SQL1)
>> WHERE name IN (SELECT name FROM SQL2)
>> AND name IN (SELECT name FROM SQL3)
>> AND name IN (SELECT name FROM SQL4)
>
>This gives me an incorrect syntax near WHERE
>
>I dont really under stand why neither of these work?!
>Got any other plans?

[Kevin Downs]

ooops, I always forget that - must be a mental block or something.

You *must* alias any sub-expression used in a FROM statement....
--
Regards,
Kevin

"hippo" <michal.rosik@swh.sk> wrote in message news:3a39d1cb@news.devx.com...
>
> try it this way (there is an "AS X" clause added and X.Name instead of
ORG.Name
> in SELECT statement, GROUP and ORDER clause):
> > SELECT X.Name
> > FROM
> > (
> > (SELECT DISTINCT ORG.Name
> > blah blah SQL 1)
> > UNION
> > (SELECT DISTINCT ORG.Name
> > blah blah SQL 2)
> > UNION
> >
> > (SELECT DISTINCT ORG.Name
> > blah blah SQL 3)
> > UNION
> >
> > (SELECT DISTINCT ORG.Name
> > blah blah SQL 4)
> > ) AS X
> > GROUP BY X.Name
> > HAVING COUNT(*) = 4
> > ORDER BY X.Name
>
> nice day
>
> hippo
>
> "tilley" <daniel.tilley@thomastelford.com> wrote:
> >
> >Sorry to be a pain in the *** but...
> >
> >I cant get either of your ideas to work:
> >
> >IDEA ONE
> >
> >> SELECT ORG.Name
> >> FROM
> >> (
> >> (SELECT DISTINCT ORG.Name
> >> blah blah SQL 1)
> >> UNION
> >> (SELECT DISTINCT ORG.Name
> >> blah blah SQL 2)
> >> UNION
> >>
> >> (SELECT DISTINCT ORG.Name
> >> blah blah SQL 3)
> >> UNION
> >>
> >> (SELECT DISTINCT ORG.Name
> >> blah blah SQL 4)
> >> )
> >> GROUP BY ORG.Name
> >> HAVING COUNT(*) = 4
> >> ORDER BY ORG.Name
> >
> >This type of query works fine:
> >---------------------------------
> >SELECT ORG.Name
> >FROM Organisation AS ORG,
> > Services AS SERV
> >WHERE ORG.OrgID = SERV.OrgID AND
> > SERV.OrgID = 1
> >GROUP BY ORG.Name
> >---------------------------------
> >
> >but as soon as i use nesting it say incorrect syntax near GROUP
> >---------------------------------
> >SELECT ORG.Name
> >FROM
> > (SELECT DISTINCT ORG.Name
> > FROM Organisation AS ORG,
> > Services AS SERV
> > WHERE ORG.OrgID = SERV.OrgID AND
> > SERV.OrgID = 1)
> >GROUP BY ORG.Name
> >---------------------------------
> >
> >IDEA 2
> >
> >> SELECT name
> >> FROM (SQL1)
> >> WHERE name IN (SELECT name FROM SQL2)
> >> AND name IN (SELECT name FROM SQL3)
> >> AND name IN (SELECT name FROM SQL4)
> >
> >This gives me an incorrect syntax near WHERE
> >
> >I dont really under stand why neither of these work?!
> >Got any other plans?
>