Help with SQL Query

[b_naick]

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--]

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.

I want to be able to query for all event_id's such that event_type = 2 and event_type <> 1. <<

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]

b_*****@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/AwUBQpTZ5IechKqOuFEgEQKIrgCfeW81ytgRIXUnl//jAA0RU8zZwLQAoOPN
UBuYtqvs/JqhLjVuFIYYTzqF
=aqWw
-----END PGP SIGNATURE-----

[Erland Sommarskog]

(b_*****@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, es****@sommarskog.se

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