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.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.|||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/AwUBQpTZ5IechKqOuFEgEQKIrgCfeW81ytgRIXUnl//jAA0RU8zZwLQAoOPN
UBuYtqvs/JqhLjVuFIYYTzqF
=aqWw
--END PGP SIGNATURE--|||(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
