Looping in SQL Server 2008
-
Hi
I am new to SQL. I have two table in the following format
Details_Emp Emp_Desig
id Form_id Employee_No id Employee_No Desg_Id
1 1265 125504 1 125504 12
2 1269 121186 2 121186 10
3 null 129876 3 129876 9
So both the table has Employee_no in common. I need to do a left outer join Emp_desig with Details_Emp on Employee_No, but condition is that for Details_Emp whose Form_id is null you have to show No for not null you have to show Yes.
-
What you posted, and the title of your post, are two different things. I don't think you need to loop at all.
What you need is to write up your OUTER JOIN (Left or Right is up to you) and specify a WHERE clause. Have you used Books Online (BOL) to look up the syntax for JOIN and SELECT yet?
If so, please post what you've already tried and tell us why it isn't giving you the results that you want.
BTW: The way you posted your tables, I can't tell one from the other. Since they're both on the same lines, they look like 1 table, which won't help us help you.
Another Edit: Sorry, ignore the WHERE comment. Look up CASE in Books Online. That will assist with your Yes / No problem.
-
If you really want some good help, please see the first link in my signature line below. If you can provide the data in a manner cited in that article, people will respond with tested code and, usually, very quickly because you've made it easy for them.
--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) -
Hi,
i am not so clear about your requirement.
As per your question, i think this solution may work for you, try it once and let me know
CREATE TABLE #Details_Emp
(
id BIGINT,
Form_id BIGINT,
Employee_No BIGINT
)
CREATE TABLE #Emp_Desig
(
id BIGINT,
Employee_No BIGINT,
Desg_Id BIGINT
)
INSERT INTO #Details_Emp
(
id,
Form_id,
Employee_No
)
SELECT 1,
1265,
125504
UNION ALL
SELECT 2,
1269,
121186
UNION ALL
SELECT 3,
NULL,
129876
INSERT INTO #Emp_Desig
(
id,
Employee_No,
Desg_Id
)
SELECT 1,
125504,
12
UNION ALL
SELECT 2,
121186,
10
UNION ALL
SELECT 3,
129876,
9
SELECT *
FROM #Details_Emp
SELECT *
FROM #Emp_Desig
SELECT de.id,
CASE WHEN de.Form_id IS NULL THEN 'NO'
ELSE 'Yes'
END,
de.Employee_No
FROM #Details_Emp de
LEFT JOIN #Emp_Desig ed ON de.Employee_No = ed.Employee_No
-
output :
-
output :
id(No column name)Employee_No
1Yes125504
2Yes121186
3NO129876
-
EDIT: Nevermind. Thought this was the OP.
-
Thank You, my porblem is solved using the case statement.
-
Glad we could help.
BTW, I recommend getting a copy of Books Online. It's a wonderful resource and will be your best friend.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply