April 11, 2014 at 9:28 pm
Having an absolute rough day today.. not able to write the simplest of simplest queries.. love being a beginner again 😀 .. I think I should be resting after this.. ok, lets get back to business..
DECLARE @Emp TABLE
(
Empid INT,
EmpName VARCHAR(10)
)
DECLARE @Proj TABLE
(
ProjID INT,
ProjName VARCHAR(10)
)
DECLARE @EmpProj TABLE
(
EmpID INT,
ProjID INT
)
INSERT INTO @Emp VALUES(1, 'Tony'), (2,'Romo')
INSERT INTO @Proj VALUES(1, 'Project 1'), (2, 'Project 2')
INSERT INTO @EmpProj VALUES(1, 1),(2, 1), (2, 2)
I want employees only if they are part of all projects. So the output is only "Romo" because he is present in both the projects.
People, this is not a college assignment. I just modified my requirement this way.. 😛
Thanks in advance..
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
April 11, 2014 at 11:54 pm
Here's one way. The expected indexes are essential.
DECLARE @Emp TABLE
(
Empid INT PRIMARY KEY CLUSTERED,
EmpName VARCHAR(10)
)
DECLARE @Proj TABLE
(
ProjID INT PRIMARY KEY CLUSTERED,
ProjName VARCHAR(10)
)
DECLARE @EmpProj TABLE
(
EmpID INT,
ProjID INT,
PRIMARY KEY CLUSTERED (EmpID, ProjID)
)
INSERT INTO @Emp SELECT 1, 'Tony' UNION ALL SELECT 2,'Romo'
INSERT INTO @Proj SELECT 1, 'Project 1' UNION ALL SELECT 2, 'Project 2'
INSERT INTO @EmpProj SELECT 1, 1 UNION ALL SELECT 2, 1 UNION ALL SELECT 2, 2
;
WITH
cte AS
( --=== Find employees NOT in a project
SELECT e.EmpID, p.ProjID FROM @Emp e CROSS JOIN @Proj p
EXCEPT
SELECT EmpID, ProjID FROM @EmpProj
) --=== Find employess NOT in the list above
SELECT *
FROM @Emp
WHERE EmpID NOT IN (SELECT EmpID FROM CTE)
;
--Jeff Moden
Change is inevitable... Change for the better is not.
April 12, 2014 at 11:05 am
Thanks Jeff.. The indexes you specified are present. I have ended up with like the same query. I was thinking if there is any better approach than using the cross Join.
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
April 12, 2014 at 11:54 am
a4apple (4/12/2014)
I was thinking if there is any better approach than using the cross Join.
Don't know if this is better, at least it is slightly different 😎
DECLARE @Emp TABLE (Empid INT,EmpName VARCHAR(10)) ;
DECLARE @Proj TABLE (ProjID INT,ProjName VARCHAR(10)) ;
DECLARE @EmpProj TABLE (EmpID INT,ProjID INT) ;
INSERT INTO @Emp VALUES(1, 'Tony'), (2,'Romo') ;
INSERT INTO @Proj VALUES(1, 'Project 1'), (2, 'Project 2') ;
INSERT INTO @EmpProj VALUES(1, 1),(2, 1), (2, 2) ;
;WITH PROD_COUNT(PCNT) AS
(SELECT COUNT(*) AS PCNT FROM @Proj)
SELECT
X.Empid
,X.EmpName
FROM
(
SELECT
E.Empid
,E.EmpName
,ROW_NUMBER() OVER
(
PARTITION BY E.Empid
ORDER BY (SELECT NULL)
) AS EMP_RID
FROM @EmpProj P
INNER JOIN @Emp E
ON P.EmpID = E.Empid
) AS X
INNER JOIN PROD_COUNT PC ON X.EMP_RID = PC.PCNT;
April 12, 2014 at 8:21 pm
a4apple (4/12/2014)
Thanks Jeff.. The indexes you specified are present. I have ended up with like the same query. I was thinking if there is any better approach than using the cross Join.
There are, indeed, other approaches that will probably be faster than the CROSS JOIN approach (like Eirikur's great shot at this). My concern is that I said that the indexes were essential. In the case of the CROSS JOIN versions to solve this problem, they're not really so essential. They just make things run a bit faster. In the "count" type of solutions, the UNIQUE index on the bridge table is absolutely essential to accuracy. While that may not seem like it should ever be a problem, I've personally witnessed an idiot "developer" pitch a fit to an even bigger idiot "DBA" that he keeps getting primary key violations on the bridge table and that moroff of a "DBA" removed the unique index from the bridge table. I wanted to kill him on the spot to make sure that very shallow gene pool ended right there and then. 🙂
Of course, the duplicated entries into the bridge table broke all of the code that did such calculations as what you've identified.
I love speed. To me, it's one of the most important factors in the world of databases. The only thing more important to me than speed is accuracy. My recommendation is that when it comes to accuracy, expect the unexpected and make sure it will be bullet-proof for accuracy over the long haul even if the code takes longer to run.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply