SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can some pull off this query for me..


Can some pull off this query for me..

Author
Message
a4apple
a4apple
SSC-Addicted
SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)

Group: General Forum Members
Points: 492 Visits: 406
Having an absolute rough day today.. not able to write the simplest of simplest queries.. love being a beginner again :-D .. 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.. :-P

Thanks in advance..

Good Luck Smile .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)

Group: General Forum Members
Points: 204470 Visits: 41951
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
a4apple
a4apple
SSC-Addicted
SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)

Group: General Forum Members
Points: 492 Visits: 406
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 Smile .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38628 Visits: 19425
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 Cool

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;


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)

Group: General Forum Members
Points: 204470 Visits: 41951
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search