May 12, 2016 at 10:05 am
rk1980factor (5/12/2016)
Chris, Looks like you solution might work. Only point i forgot to mention was that both the views(tables) are in different database
On the same server? Then use three-part naming.
On different servers? You might have issues with performance.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 12, 2016 at 10:22 am
it is on same server but the 3 columns we are comparing have different names in both tables, not sure if that can be the issue
May 12, 2016 at 10:42 am
Chris i believe your solution will work with except between two queries. One major problem with 3rd column comparision. In first table value of 3rd column is 0 but in second table the value of 3rd column is NULL. technically both are different but i want them to consider as same. Because of that issue it is giving me all the rows as result.
To give you one example: Org_Num solumn in Employee table has value 0 in many rows, Org_Num column in Class table as value "NULL" in many rows. But when i run this query
with EXCEPT
It displays all the rows are different. I want both 0 and NULL should consider same, should match and don't want that to consider different value.
May 12, 2016 at 1:49 pm
In that case, just wrap your columns that potentially have NULLs in them with ISNULL(column, 0). In the case you gave, you could do this:
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
EXCEPT
SELECT [Emp_no.], Org_Code, ISNULL(Org_Num,0) FROM #Class
-- get rows which are unique to Class:
SELECT [Emp_no.], Org_Code, ISNULL(Org_Num,0) FROM #Class
EXCEPT
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
May 12, 2016 at 2:04 pm
I might be missing something, but couldn't this be done in a single query like so?
SELECT
*
FROM
#Employee Employee
FULL OUTER JOIN
#Class Class
ON
IsNull(Employee.[Emp_no.], '') = IsNull(Class.[Emp_no.], '')
ANDIsNull(Employee.Org_Code, '') = IsNull(Class.Org_Code, '')
ANDIsNull(Employee.Org_Num, '') = IsNull(Class.Org_Num, '')
WHERE
(
Employee.[Emp_no.] IS NULL
AND Employee.Org_Code IS NULL
AND Employee.Org_Num IS NULL
)
OR
(
CLASS.[Emp_no.] IS NULL
AND CLASS.Org_Code IS NULL
AND CLASS.Org_Num IS NULL
)
May 13, 2016 at 8:16 am
wish this can be done easily with single query :
actually this query provided by previous user does work:
SELECT Emp_no, Org_Code, Org_Num FROM [dbo].[Employee]
EXCEPT
SELECT Emp_no, Org_Code, Org_Num FROM [dbo].[Class]
-- get rows which are unique to Class:
SELECT Emp_no, Org_Code, Org_Num FROM [dbo].[Class]
EXCEPT
SELECT Emp_no, Org_Code, Org_Num FROM [dbo].[Employee]
but it gives me result in two separate windows.
I want the result should be in same window
May 13, 2016 at 8:17 am
below query gives me wrong result , it seem to list all the values
SELECT
*
FROM
#Employee Employee
FULL OUTER JOIN
#Class Class
ON
IsNull(Employee.[Emp_no.], '') = IsNull(Class.[Emp_no.], '')
ANDIsNull(Employee.Org_Code, '') = IsNull(Class.Org_Code, '')
ANDIsNull(Employee.Org_Num, '') = IsNull(Class.Org_Num, '')
WHERE
(
Employee.[Emp_no.] IS NULL
AND Employee.Org_Code IS NULL
AND Employee.Org_Num IS NULL
)
OR
(
CLASS.[Emp_no.] IS NULL
AND CLASS.Org_Code IS NULL
AND CLASS.Org_Num IS NULL
)
May 13, 2016 at 8:18 am
Looks like this query displays the correct result but it display on separate window. i want one query which will show me one result:
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
EXCEPT
SELECT [Emp_no.], Org_Code, ISNULL(Org_Num,0) FROM #Class
-- get rows which are unique to Class:
SELECT [Emp_no.], Org_Code, ISNULL(Org_Num,0) FROM #Class
EXCEPT
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
May 13, 2016 at 8:20 am
rk1980factor (5/13/2016)
Looks like this query displays the correct result but it display on separate window. i want one query which will show me one result:SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
EXCEPT
SELECT [Emp_no.], Org_Code, ISNULL(Org_Num,0) FROM #Class
-- get rows which are unique to Class:
SELECT [Emp_no.], Org_Code, ISNULL(Org_Num,0) FROM #Class
EXCEPT
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
Set each query up as a CTE then concatenate them using UNION ALL.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 13, 2016 at 8:21 am
rk1980factor (5/13/2016)
below query gives me wrong result , it seem to list all the valuesSELECT
*
FROM
#Employee Employee
FULL OUTER JOIN
#Class Class
ON
IsNull(Employee.[Emp_no.], '') = IsNull(Class.[Emp_no.], '')
ANDIsNull(Employee.Org_Code, '') = IsNull(Class.Org_Code, '')
ANDIsNull(Employee.Org_Num, '') = IsNull(Class.Org_Num, '')
WHERE
(
Employee.[Emp_no.] IS NULL
AND Employee.Org_Code IS NULL
AND Employee.Org_Num IS NULL
)
OR
(
CLASS.[Emp_no.] IS NULL
AND CLASS.Org_Code IS NULL
AND CLASS.Org_Num IS NULL
)
Do you mean all the values as in all the columns instead of just the three key columns?
You can change the * to whatever columns you want to see.
May 13, 2016 at 9:35 am
don't know anything about CTE's
i tried this query after reading some articles but it does not work
;WITH CTE1 AS SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
EXCEPT
SELECT [Emp_no.], Org_Code, Org_Num FROM #Class
union all
;WITH CTE2 AS
-- get rows which are unique to Class:
(SELECT [Emp_no.], Org_Code, Org_Num FROM #Class
EXCEPT
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee)
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
EXCEPT
SELECT [Emp_no.], Org_Code, Org_Num FROM #Class
UNION ALL
SELECT [Emp_no.], Org_Code, Org_Num FROM #Class
EXCEPT
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
May 13, 2016 at 9:36 am
thought it should be fairly simple to display one result with two queries or combining two queries
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
EXCEPT
SELECT [Emp_no.], Org_Code, ISNULL(Org_Num,0) FROM #Class
-- get rows which are unique to Class:
SELECT [Emp_no.], Org_Code, ISNULL(Org_Num,0) FROM #Class
EXCEPT
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
May 13, 2016 at 9:41 am
Have you tried this?
;WITH
CTE1 AS (
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
EXCEPT
SELECT [Emp_no.], Org_Code, Org_Num FROM #Class
), CTE2 AS (
SELECT [Emp_no.], Org_Code, Org_Num FROM #Class
EXCEPT
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
)
SELECT * FROM CTE1
UNION ALL
SELECT * FROM CTE2
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 13, 2016 at 12:15 pm
ChrisM@Work (5/13/2016)
Have you tried this?
;WITHCTE1 AS (
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
EXCEPT
SELECT [Emp_no.], Org_Code, Org_Num FROM #Class
), CTE2 AS (
SELECT [Emp_no.], Org_Code, Org_Num FROM #Class
EXCEPT
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
)
SELECT * FROM CTE1
UNION ALL
SELECT * FROM CTE2
No need to set up two CTEs.
(
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
EXCEPT
SELECT [Emp_no.], Org_Code, Org_Num FROM #Class
)
UNION ALL
(
SELECT [Emp_no.], Org_Code, Org_Num FROM #Class
EXCEPT
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
)
You can simply use parentheses to ensure that the EXCEPT clauses are evaluated before the UNION clause.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 16, 2016 at 7:53 am
so both of this query works:
;WITH
CTE1 AS (
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
EXCEPT
SELECT [Emp_no.], Org_Code, Org_Num FROM #Class
), CTE2 AS (
SELECT [Emp_no.], Org_Code, Org_Num FROM #Class
EXCEPT
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
)
SELECT * FROM CTE1
UNION ALL
SELECT * FROM CTE2
No need to set up two CTEs.
(
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
EXCEPT
SELECT [Emp_no.], Org_Code, Org_Num FROM #Class
)
UNION ALL
(
SELECT [Emp_no.], Org_Code, Org_Num FROM #Class
EXCEPT
SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee
)
final question on these is, i still want to compare all 3 columns from both tables but just want to display one column only (Emp_no.) without duplicates.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply