CELKO (9/15/2012)
>> I'm having trouble isolating some rows in a table. Its an audit table that can have the same values repeated in some fields [sic: columns are not fields]. See the table below. I'm only interested in selecting the row where EMG ID is 1267202 as it has a something_backscan value of 1 AND 0 instead of only 1 or only 0. (The real life audit table has many more rows but these are the key ones I need for my query) <<It would really help if we has DDL, a key, constraints and proper data element names. This is a form of relational division.
CREATE TABLE Foobar
(emg_id VARCHAR(7) NOT NULL
CHECK (emg_id LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
vague_reference VARCHAR(8) NOT NULL
CHECK (vague_reference
LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
something_backscan SMALLINT DEFAULT 0 NOT NULL
CHECK (something_backscan IN (0,1));
INSERT INTO Foobar
VALUES
('1267202', '32655185', 1), ('1267202', '32655185', 0),
('1239667', '33264522', 1), ('1239667', '33264522', 1),
('1234000', '33674689', 0), ('1234000', '33674689', 0),
('1235670', '33674689', 1),
('1237830', '33674689', 0);
SELECT emg_id, vague_reference
FROM Foobar
GROUP BY emg_id, vague_reference
HAVING MIN(something_backscan) = 0
AND MAX(something_backscan) = 1;
Mr. Celko,
It would be nice if you would test your code before you post, especially since you are the worlds formost SQL expert. The following code errors in SQL Server 2008 R2 and I really don't feel like figuring out why. That is going to have to be up to you.
CREATE TABLE Foobar
(emg_id VARCHAR(7) NOT NULL
CHECK (emg_id LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
vague_reference VARCHAR(8) NOT NULL
CHECK (vague_reference
LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
something_backscan SMALLINT DEFAULT 0 NOT NULL
CHECK (something_backscan IN (0,1));
Here is the error message:
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ';'.