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;

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));

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ';'.

eamonn.byrne (9/14/2012)

---

Yep that definately works. It took you 5 seconds to fix what had me scratching my head for a couple of hours.

Cheers!

Jeff Moden (9/16/2012)

---

Before you use it, Joe is right.

1. What is the key here? Is it just EmgID or is it EmgID and Reference together.

2. Regardless of what the answer to 1 is, is there anything special you want done for an EmgID with multiple Reference values?