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

need solution and feedback to the below question!! Expand / Collapse
Author
Message
Posted Monday, July 15, 2013 9:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:47 AM
Points: 73, Visits: 300
here is the sample data .

create table empp(eno int,ename varchar(25),dept varchar(3))

insert into empp values(101,'raghava','hr')

insert into empp values(102,'krish','hr')

insert into empp values(103,'venkat','fin')



create table dept(dno int, dname varchar(3))

insert into dept values(1,'hr')
insert into dept values(2,'fin')

insert into dept values(3,'mkt')


all i need the deptname where there are no employees in it.

for above the example , it has to be 'mkt' as result.

Thanks for your help!!!
Post #1473742
Posted Monday, July 15, 2013 10:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:10 PM
Points: 13,330, Visits: 12,829
select * 
from dept d
left join empp e on e.dept = d.dname
where e.dept is null

Why do you the name of the department in your employee table. You should have the department number instead. Otherwise, what is the point of having a department table?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1473754
Posted Monday, July 15, 2013 10:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:47 AM
Points: 73, Visits: 300
thanks.. yes you were right... i need to use deptno only...

Post #1473756
Posted Monday, July 15, 2013 10:17 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 4,066, Visits: 9,235
Just another way of doing it.

select * 
from dept d
WHERE NOT EXISTS( SELECT 1 FROM empp e WHERE e.dept = d.dname)




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1473759
Posted Monday, July 15, 2013 10:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:47 AM
Points: 73, Visits: 300
Thanks Luis!!
Post #1473761
Posted Monday, July 15, 2013 10:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:10 PM
Points: 13,330, Visits: 12,829
Thanks Luis for posting the "other" option.

To the OP, which one is better? That is not as simple as it may seem. There are some considerations about the actual table structure to consider.

You might want to read Gail's article that explains the difference between these two approaches and the performance considerations around each of them.

http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1473766
Posted Monday, July 15, 2013 5:53 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Perhaps an even simpler solution:


--Sample data

IF OBJECT_ID('tempdb..#empp') IS NOT NULL
DROP TABLE #empp

CREATE TABLE #empp
(
eno INT
,ename VARCHAR(25)
,dept INT
)

INSERT INTO #empp
VALUES (101,'raghava',1)
INSERT INTO #empp
VALUES (102,'krish',1)
INSERT INTO #empp
VALUES (103,'venkat',3)

IF OBJECT_ID('tempdb..#dept') IS NOT NULL
DROP TABLE #dept

CREATE TABLE #dept
(
dno INT
,dname VARCHAR(3)
)

INSERT INTO #dept
VALUES (1,'hr')
INSERT INTO #dept
VALUES (2,'fin')
INSERT INTO #dept
VALUES (3,'mkt')


Returns only the missing dept number:


SELECT
dno
FROM
#dept AS d
EXCEPT
SELECT
dept
FROM
#empp AS e


 
Post #1473934
Posted Monday, July 15, 2013 9:58 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 4:23 PM
Points: 35,821, Visits: 32,494
Steven Willis (7/15/2013)
Perhaps an even simpler solution:
 


That would have been my first choice for this. I believe that I'd reverse the order of the tables, though. Reason being is to look for NULLs and other anomolies.


--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 #1473965
Posted Tuesday, July 16, 2013 7:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:10 PM
Points: 13,330, Visits: 12,829
Steven Willis (7/15/2013)
Perhaps an even simpler solution:
 


Nice. I always forget about except, not really sure why. Thanks for reminding that is in the toolbox!!!


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1474113
Posted Tuesday, July 16, 2013 7:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:47 AM
Points: 73, Visits: 300
Thansk folks!!! i learned a lot here by posting the question...

thanks again!!!!
Post #1474117
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse