Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server :: Need Help with following Query

Posted on 2012-08-28
6
Medium Priority
?
480 Views
Last Modified: 2012-08-28
Hi Experts,

I have two tables
TAB1
EMPID   EMPName
1             "AAA"
2             "BBB"
3              "CCC"

TAB2
WorkID  EMPID  WorkName
1             2           "Draft"
2.            1           "Merge"

My requirement is i need to check if EMPID from Tab1 Exists in tab2,  If exists
 then i need to return a row from tab1 as

select EmpID, EmpName,  (select EmpID from TAb2 where EmpID=EmpID) as Assigned.
o/p
EmpID     EMPname       Add'nalColumn
2             "BBB"              "Assigned"
3.             "CCC"               "Unassigned"

if there is EmpID in Tab2 then the value of  Add'nalColumn  should be 'Assigned'  else 'UnAssigned'

How can i write this Query , Plz help
0
Comment
Question by:B_Pati
6 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38341331
Using a JOIN will insure that the return records are only the rows where there are related records in both tables.

Give this a whirl...

SELECT Tab1.EmpID, Tab2.EmpName
FROM Tab1
    JOIN Tab2 ON Tab1.EmpID = Tab2.EmpID
0
 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 750 total points
ID: 38341332
SELECT EmpID, EMPname, CASE WHEN WorkID IS NULL THEN 'Assigned' ELSE 'Unassigned' END 'Add''nalColumn'
FROM TAB1 LEFT JOIN TAB2 ON TAB1.EMPID =TAB2.EMPID
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38341335
<correction>

SELECT Tab1.EmpID, Tab1.EmpName
FROM Tab1
    JOIN Tab2 ON Tab1.EmpID = Tab2.EmpID
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 13

Accepted Solution

by:
LIONKING earned 750 total points
ID: 38341451
Since you want to evaluate the existence of the value(s) in your second table, you can use a left outer join and check for null values.

Something like this:

SELECT Tab1.EmpID, Tab1.EmpName,
CASE WHEN Tab2.EmpID IS NULL THEN 'Unassigned' ELSE 'Assigned' END IsAssigned
FROM Tab1 LEFT OUTER JOIN Tab2 ON
Tab1.EmpID = Tab2.EmpID

Give it a try and let us know.
0
 
LVL 1

Author Comment

by:B_Pati
ID: 38341600
Using Above tow solutions The Query below helped me

SELECT    EmpID, EmpName, Add'nalColumn=
      CASE  WHEN (select  workName from tab2 where EmpID= EmpID)
        IS NULL THEN 'UnAssigned'
         ELSE 'Assigned'
      END
 
FROM Tab1.

Thanks all for helping me on this
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38341638
You need to specify the EmpID=EmpID... has to be: Tab1.EmpID=Tab2.EmpID, else everything will be assigned. Anyway, the result is the same as with a left join. It's the same principal. You can also use EXISTS, if you prefer.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question