Microsoft SQL Server forum - Help with SQL Query

Author

Help with SQL Query

b_naick@yahoo.ca

2005-05-25, 1:23 pm

I need help building the following query..

My table has the following schema: eventID, typeID

Sample Rows:

1,1
1,2
1,3
2,1
3,2
3,2
4,3
4,4
5,2

I want to be able to query for all eventID's such that type = 2 and
type <> 1. So the result should be

3,2
4,2

The result should NOT include 1,2 because eventID 1 is also "related"
to typeID 1 and 3.

--CELKO--

2005-05-25, 8:24 pm

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Your personal pseudo-code is wrong at many levels; did
you mean this?

CREATE TABLE EventSchedules
(event_id INTEGER NOT NULL
REFERENCES Events (event_id),
event_type INTEGER NOT NULL
CHECK (event_type > 0), --assumption
PRIMARY KEY (event_id, event_type)); --requirement!

INSERT INTO EventSchedules VALUES (1,1);
INSERT INTO EventSchedules VALUES (1,2);
INSERT INTO EventSchedules VALUES (1,3);
INSERT INTO EventSchedules VALUES (2,1);
INSERT INTO EventSchedules VALUES (3,2);
INSERT INTO EventSchedules VALUES (3,2);-- removed dup row!!
INSERT INTO EventSchedules VALUES (4,3);
INSERT INTO EventSchedules VALUES (4,4);
INSERT INTO EventSchedules VALUES (5,2);

A data element name like "type_id" makes no sense. Either it is an
identifier for a particular kind of entity or it is some kind of code
for an attribute. It cannot be both an attribute and an entity. You
might want to get a book on data modeling and the ISO-11179 Standards.
[color=darkred]

Here is one way.

SELECT event_id
FROM EventSchedules
GROUP BY event_id
HAVING MIN(event_type) > 1
AND MAX (CASE WHEN event_type <> 2 THEN 0 ELSE 2 END) = 2;

And I am sure that someone will come up with a self-join solution, too.

MGFoster

2005-05-25, 8:24 pm

b_naick@yahoo.ca wrote:
> I need help building the following query..
>
> My table has the following schema: eventID, typeID
>
> Sample Rows:
>
> 1,1
> 1,2
> 1,3
> 2,1
> 3,2
> 3,2
> 4,3
> 4,4
> 5,2
>
> I want to be able to query for all eventID's such that type = 2 and
> type <> 1. So the result should be
>
> 3,2
> 4,2
>
> The result should NOT include 1,2 because eventID 1 is also "related"
> to typeID 1 and 3.
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Shouldn't the last pair be 5,2, since there isn't any 4,2.

Try,

SELECT DISTINCT eventID, typeID
FROM t as t1
WHERE typeID = 2
AND eventID NOT IN (SELECT eventID FROM t WHERE typeID != 2)

Change table name "t" to your table's true name.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/ AwUBQpTZ5IechKqOuFEg
EQKIrgCfeW81ytgRIXUn
l//jAA0RU8zZwLQAoOPN
UBuYtqvs/JqhLjVuFIYYTzqF
=aqWw
-----END PGP SIGNATURE-----

Erland Sommarskog

2005-05-25, 8:24 pm

(b_naick@yahoo.ca) writes:
> I need help building the following query..
>
> My table has the following schema: eventID, typeID
>
> Sample Rows:
>
> 1,1
> 1,2
> 1,3
> 2,1
> 3,2
> 3,2
> 4,3
> 4,4
> 5,2
>
> I want to be able to query for all eventID's such that type = 2 and
> type <> 1. So the result should be
>
> 3,2
> 4,2
>
> The result should NOT include 1,2 because eventID 1 is also "related"
> to typeID 1 and 3.

I assume that desired result is

3,2
5,2

Else there is something I don't understand at all.

This could be a good query:

SELECT *
FROM tbl a
WHERE a.type = 2
AND NOT EXISTS (SELECT *
FROM tbl b
WHERE a.eventID = b.eventID
AND EXISTS (SELECT *
FROM tbl c
WHERE c.eventID = b.eventID
ABD c.type = 1))

Since you did not include CREATE TABLE and INSERT statements, I
have not tested this.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links