need solution and feedback to the below question!!

Question

Post reply

need solution and feedback to the below question!!

raghava_tg

Ten Centuries

Points: 1014
More actions
July 15, 2013 at 9:52 am

#295545

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!!!:-):-)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

raghava_tg Ten Centuries Points: 1014 More actions · Answer 2

thanks.. yes you were right... i need to use deptno only...

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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.
General Disclaimer:
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?

How to post data/code on a forum to get the best help: Option 1 / Option 2

raghava_tg Ten Centuries Points: 1014 More actions · Answer 4

raghava_tg

Ten Centuries

Points: 1014

July 15, 2013 at 10:21 am

#1632558

Thanks Luis!!

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

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/[/url]

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

raghava_tg Ten Centuries Points: 1014 More actions · Answer 9

Thansk folks!!! i learned a lot here by posting the question...

thanks again!!!!