SQL Union / Intersection

[Will]

I'm trying to build dynamic sql from a string passed by a calling
application. Let's assume for this discussion that the user can pass a
string of letters with these logical operators ("and", "or", and "and
not") seperating them. Each letter can be rebuilt into a sub query
that will search for people in a table by their middle initial. For
example,

X and Y

(select SSN from tblPerson where MiddleInitial = 'X') UNION
(select SSN from tblPerson where MiddleInitial = 'Y')

This seems pretty easy with the and operator (UNION) but how can I do
"or" and "and not"? I remember from a SQL class I had 10 years ago
that there was an INTERSECTION operator but it appears that T-SQL
doesn't support it. The closest option is EXIST and NOT EXIST but
these can not be simply inserted between two sub queries (I think),
they require the user of a where clause. It would obviously work in
the example above but in the more complecated example below it wouldn't
be an easy replacement of the operators and sub queries.

X and (Y or J) and not L

So, the bottom line is that I have no problem replacing the letters
with the appropriate sub query but I'm looking for a way to replace the
logical operator with SQL syntax that will mimic the logical operator.


I hope this makes sense. : )

Will Wirtz

[kevindotcar@gmail.com]

Re: SQL Union / Intersection



Will wrote:

[---]
[color=blue]
>
> X and Y
>
> (select SSN from tblPerson where MiddleInitial = 'X') UNION
> (select SSN from tblPerson where MiddleInitial = 'Y')
>
> This seems pretty easy with the and operator (UNION) but how can I do
> "or" and "and not"?[/color]

[---]

Hi Will,

Try Wrapping your qry inside a constraining qry;

SELECT SSN FROM (
(select SSN from tblPerson where MiddleInitial = 'X')
UNION
(select SSN from tblPerson where MiddleInitial = 'Y'))
WHERE LEFT(SSN,3) = "333"
OR LEFT(SSN,3) = "444"


HTH-

kDot

[ZeldorBlat]

Re: SQL Union / Intersection

Why use a sub-query at all?

select SSN
from tblPerson
where MiddleInitial = 'X'
or MiddleInitial = 'Y'

select SSN
from tblPerson
where MiddleInitial = 'X'
and MiddleInitial = 'Y'

select SSN
from tblPerson
where MiddleInitial = 'X'
and MiddleInitial != 'Y'

[Will]

Re: SQL Union / Intersection

I should probably use a different example as the one I described didn't
convey what I wanted. my fault. let me try again.

new example. Let's say the calling app passes american state codes (CO
= colorado, WA = Washington, etc) Now, what we're trying to do is find
people who have visited these states. So let's take another look at a
complex scenario.

CO and (IA or MO) and not AK

So I need to turn this into a query that will return the SSN of all
people who have ever visited Colorado and either Iowa or Missouri but
have never been to Alaska. What I would like to do is turn this into
something like this:

(select SSN from tblVistied where StateCode = 'CO')
INTERSECTION
(
(select SSN from tblVistied where StateCode = 'IA')
UNION
(select SSN from tblVistied where StateCode = 'MO')
)
DIFFERENCE
(select SSN from tblVistied where StateCode = 'AK')


Because I don't know the order or the number of state codes that will
be passed I want to avoid reformatting the entire statement. I would
like to just substitute SQL for statecodes and SQL for the logical
operators. Does anyone have any ideas?

[Erland Sommarskog]

Re: SQL Union / Intersection

Will (WillCWirtz@Yah oo.com) writes:[color=blue]
> I'm trying to build dynamic sql from a string passed by a calling
> application. Let's assume for this discussion that the user can pass a
> string of letters with these logical operators ("and", "or", and "and
> not") seperating them. Each letter can be rebuilt into a sub query
> that will search for people in a table by their middle initial. For
> example,
>
> X and Y
>
> (select SSN from tblPerson where MiddleInitial = 'X') UNION
> (select SSN from tblPerson where MiddleInitial = 'Y')
>
> This seems pretty easy with the and operator (UNION) but how can I do
> "or" and "and not"? I remember from a SQL class I had 10 years ago
> that there was an INTERSECTION operator but it appears that T-SQL
> doesn't support it.[/color]

Actually, SQL 2005 does suppor both INTERSECT and EXISTS.

As for SQL 2000, Itzik Ben-Gan wrote about this is his SQL Server
Magazine column some time ago, and how you could implement them in
SQL 2000. See
http://www.windowsitpro.com/Article/...321/40321.html.
(This is a subscriber-only article, though.)



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Greg D. Moore \(Strider\)]

Re: SQL Union / Intersection


"ZeldorBlat " <zeldorblat@gma il.com> wrote in message
news:1116962785 .380752.74920@g 49g2000cwa.goog legroups.com...[color=blue]
> Why use a sub-query at all?
>
> select SSN
> from tblPerson
> where MiddleInitial = 'X'
> or MiddleInitial = 'Y'
>
> select SSN
> from tblPerson
> where MiddleInitial = 'X'
> and MiddleInitial = 'Y'[/color]

This one won't work, since a MiddleInitial can't be both X AND Y.
[color=blue]
>
> select SSN
> from tblPerson
> where MiddleInitial = 'X'
> and MiddleInitial != 'Y'
>[/color]

[--CELKO--]

Re: SQL Union / Intersection

I think what you want is the ability to load tables with criteria and
not have to use dynamic SQL:

skill = Java AND (skill = Perl OR skill = PHP)

becomes the disjunctive canonical form:

(Java AND Perl) OR (Java AND PHP)

which we load into this table:

CREATE TABLE Query
(and_grp INTEGER NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (and_grp, skill));

INSERT INTO Query VALUES (1, 'Java');
INSERT INTO Query VALUES (1, 'Perl');
INSERT INTO Query VALUES (2, 'Java');
INSERT INTO Query VALUES (2, 'PHP');

Assume we have a table of job candidates:

CREATE TABLE Candidates
(candidate_name CHAR(15) NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (candidate_name , skill));

INSERT INTO Candidates VALUES ('John', 'Java'); --winner
INSERT INTO Candidates VALUES ('John', 'Perl');
INSERT INTO Candidates VALUES ('Mary', 'Java'); --winner
INSERT INTO Candidates VALUES ('Mary', 'PHP');
INSERT INTO Candidates VALUES ('Larry', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Larry', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Moe', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Java');
INSERT INTO Candidates VALUES ('Celko', 'Java'); -- loser
INSERT INTO Candidates VALUES ('Celko', 'Algol');
INSERT INTO Candidates VALUES ('Smith', 'APL'); -- loser
INSERT INTO Candidates VALUES ('Smith', 'Algol');

The query is simple now:

SELECT DISTINCT C1.candidate_na me
FROM Candidates AS C1, Query AS Q1
WHERE C1.skill = Q1.skill
GROUP BY Q1.and_grp, C1.candidate_na me
HAVING COUNT(C1.skill)
= (SELECT COUNT(*)
FROM Query AS Q2
WHERE Q1.and_grp = Q2.and_grp);

You can retain the COUNT() information to rank candidates. For example
Moe meets both qualifications, while other candidates meet only one of
the two. You can Google "canonical disjunctive form" for more details.
This is a form of relatioanl division.

[kevindotcar@gmail.com]

Re: SQL Union / Intersection



ZeldorBlat wrote:[color=blue]
> Why use a sub-query at all?
>
> select SSN
> from tblPerson
> where MiddleInitial = 'X'
> or MiddleInitial = 'Y'[/color]
[---]

Well, my guilty secret is I'm primarily an Access guy- I only use
SQL7 for warehousing :)


kDot