April 14, 2007 at 3:51 am
Hi,
Can anyone solve this puzzle ?
Two tables Emp1 and Emp2
Emp1 Emp2
101 101
102 102
103 103
104 105
The final result should be
104
105
Good LUCK!!!
--Sandeep
April 14, 2007 at 6:41 am
I can.
And have solved this kind of tasks many times.
Is it your homework?
So, you have to do it.
_____________
Code for TallyGenerator
April 14, 2007 at 9:06 am
There are at least three SET-BASED methods you can use
1) FULL JOIN
2) UNION ALL with GROUP BY
3) UNION ALL with LEFT JOIN
N 56°04'39.16"
E 12°55'05.25"
April 14, 2007 at 9:26 am
-- Prepare sample data
DECLARE @Emp1 TABLE (Emp INT)
INSERT @Emp1
SELECT 101 UNION ALL
SELECT 102 UNION ALL
SELECT 103 UNION ALL
SELECT 104
DECLARE @Emp2 TABLE (Emp INT)
INSERT @Emp2
SELECT 101 UNION ALL
SELECT 102 UNION ALL
SELECT 103 UNION ALL
SELECT 105
-- Show the expected output 1
SELECT COALESCE(e1.Emp, e2.Emp)
FROM @Emp1 AS e1
FULL JOIN @Emp2 AS e2 ON e2.Emp = e1.Emp
WHERE e1.Emp IS NULL
OR e2.Emp IS NULL
-- Show the expected output 2
SELECT Emp
FROM (
SELECT Emp
FROM @Emp1
UNION ALL
SELECT Emp
FROM @Emp2
) AS x
GROUP BY Emp
HAVING COUNT(*) = 1
-- Show the expected output 3
SELECT e1.Emp
FROM @Emp1 AS e1
LEFT JOIN @Emp2 AS e2 ON e2.Emp = e1.Emp
WHERE e2.Emp IS NULL
UNION ALL
SELECT e2.Emp
FROM @Emp2 AS e2
LEFT JOIN @Emp1 AS e1 ON e1.Emp = e2.Emp
WHERE e1.Emp IS NULL
N 56°04'39.16"
E 12°55'05.25"
April 14, 2007 at 7:59 pm
Did you just do someone's homework, Peter? Hope you get an "A"
--Jeff Moden
Change is inevitable... Change for the better is not.
April 15, 2007 at 2:56 am
Hey, you know me...
At least I didn't write
sp_msforeachdb 'drop database ''?''' or similar.
N 56°04'39.16"
E 12°55'05.25"
April 15, 2007 at 3:34 am
He does not need your help on it.
Such a "brilliant" SQL developer will kill all projects without anyone's help.
_____________
Code for TallyGenerator
April 15, 2007 at 4:17 am
His professor will notice that the code is not written by him.
It is out of his style, if he needs to question for help.
I know some of my occasional students cheat, just by reading their suggestions to the problem I give them.
N 56°04'39.16"
E 12°55'05.25"
April 15, 2007 at 6:03 am
So, whom did you help?
_____________
Code for TallyGenerator
April 15, 2007 at 8:19 am
Oooohhh.... now that's just plain evil Hadn't thought about doing something that nasty
As for professors catching code "out of a student's style"?... According to the level of idiocy of I've experienced from people interviewing for SQL jobs, most instructors aren't catching things like this.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 15, 2007 at 7:45 pm
Since the only requirement posted was the output, this should do it.
select 104 union all select 105 order by 1
Good LUCK!!!
April 15, 2007 at 11:00 pm
Hi Peter and Micheal,
Thank you very much for your extended help .
as for "SERIGY" and "JEFF MODEN" .....i am a SQL amateur and just thought of a possibility. This ain't a homework for me !!
Also i suppose that this forum is for helping others and not to challenge anyone's capabilty. You never know what "someone" can become.
If you people got those guts....u participate in SQL contests and compete with those above you and not those who are amateur.
At the same time sorry for being candid but i felt really bad looking at your replies !!!
April 15, 2007 at 11:11 pm
Sandeep, if it was not your homework you could explain what do you need it for.
It does not look like real life task, it's more like exercise from a schoolbook.
_____________
Code for TallyGenerator
April 15, 2007 at 11:21 pm
Select * from emp1 full outer join emp2 on emp1.empno=emp2.empno
where emp1.empno is null or emp2.empno is null
April 16, 2007 at 2:02 am
Use the ANSI standard EXCEPT operator, which gives you the difference between two sets. It also exists in SQL Server 2005, and in Oracle, it is called "MINUS".
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply