October 15, 2007 at 4:04 pm
Need some help getting this to flip.
TableA
Columns:
RowID Identity int
DocID int,
DocType varchar(15),
DocValue varchar(250)
Contents separated by commas
1,50,'FName','Mary'
2,50,'LName','Jones'
3,50,'EmpID','12345'
4,62,'FName','John'
5,62,'LName','Smith'
6,71,'LName','Moore'
7,71,'FName','Henry'
8,71,'EmpID','33322'
Business Rules: not all DocIDs have three associated rows. For instance, John Smith, doesn't have an EmpID row associated with his DocID of 62.
I need a query that'll return any DocID in TableA that doesn't have a EmpID row in the table.
Ariadne
October 15, 2007 at 4:11 pm
CREATE TABLE TableA
(RowID INT IDENTITY,
DocID int,
DocType varchar(15),
DocValue varchar(250)
)
INSERT INTO tableA (docID, docType, docValue)
SELECT 50,'FName','Mary' UNION ALL
SELECT 50,'LName','Jones' UNION ALL
SELECT 50,'EmpID','12345' UNION ALL
SELECT 62,'FName','John' UNION ALL
SELECT 62,'LName','Smith' UNION ALL
SELECT 71,'LName','Moore' UNION ALL
SELECT 71,'FName','Henry' UNION ALL
SELECT 71,'EmpID','33322'
SELECT DISTINCT tableA.docID
FROM tableA
LEFT JOIN
(SELECT rowid, docID FROM tableA WHERE docType = 'EmpID') emp
ON tableA.docID = emp.docID
WHERE emp.Rowid IS null
Note: LEFT JOIN instead of NOT IN for performance considerations.
October 15, 2007 at 4:13 pm
It's easy 😉 :
SELECT A1.DocID
FROM TableA A1
LEFT JOIN TableA A2 ON A1.DocID = A2.DocID AND A2.DocType = 'EmpID'
WHERE A2.DocID IS NULL
GROUP BY A1.DocID
_____________
Code for TallyGenerator
October 15, 2007 at 4:26 pm
[font="Courier New"]
SELECT DISTINCT
DocID
FROM
TableA
WHERE
DocID NOT IN
(SELECT
DocID
FROM
TableA
WHERE
DocType = 'EmpID')[/font]
October 15, 2007 at 4:29 pm
"return any DocID in TableA that doesn't have a EmpID row"
SELECT DocID from TableA
where DocID NOT IN (SELECT DocID from TableA where DocType = 'EmpID')
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply