Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can some pull off this query for me.. Expand / Collapse
Author
Message
Posted Friday, April 11, 2014 9:28 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 10:09 AM
Points: 141, Visits: 313
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.
Post #1561173
Posted Friday, April 11, 2014 11:54 PM This worked for the OP Answer marked as solution


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1561178
Posted Saturday, April 12, 2014 11:05 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 10:09 AM
Points: 141, Visits: 313
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.
Post #1561225
Posted Saturday, April 12, 2014 11:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:49 AM
Points: 1,245, Visits: 3,615
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;

Post #1561230
Posted Saturday, April 12, 2014 8:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1561258
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse