asked on

How do I get rid of the cartesian effect

I am just selecting on a query that contain cartesian records. How do I get rid of these records in my query?

SELECT distinct newEmpid,
newLastName,
newFirstName,
newD2LRole,
newemail,
newLoc,
newLocname,
prevEmpid,
prevLastName,
prevFirstName,
prevD2LRole,
prevemail,
prevLoc,
prevLocName,
DateChanged,
Status
FROM [GSDR].[d2l].[D2L_Reporting_D2L_non_teach_delta]
where newEmpid = 'e199702275'

see attachment for the records

I am getting 4 records but I only want 2
data.xlsx

AielloJ

Butterfly2,

The reason you're getting 4 rows is because location columns are different. Do you need all those columns in your result set? If not, eliminate them from your query.

regards,

AielloJ

Randy Poole

Do you actually have a unique key id on the table? If so what is the column name?

Butterfly2

ASKER

this is a table I made myself. There is no unique key. i was actually trying to do a delta table but I figured that maybe I could get rid of these records after i made the table. But I will post my original query that made the table, maybe there is something I can do in there.

with a (newEmpid,
newLastName,
newFirstName,
newD2LRole,
newEmail,
newLoc,
NewLocname,
DateChanged
)
as

(select distinct IsNUll(empid,'') as newEmpid,
IsNull(lastname,'') as newLastName,
IsNull(firstname,'') as newFirstName,
IsNull (d2l_Role,'') as newD2LRole,
IsNull(email, '') as newemail,
IsNull(loc,'') as newLoc,
ISNULL(locname,'') as NewLocname,
convert(date,GETDATE()) as DateChanged

from d2l.D2L_Reporting_d2l_non_teach
except

select distinct IsNUll(empid,'') as newEmpid,
IsNull(lastname,'') as newLastName,
IsNull(firstname,'') as newFirstName,
IsNull (d2l_Role,'') as newD2LRole,
IsNull(email, '') as newemail,
IsNull(loc,'') as newLoc,
ISNULL(locname,'') as NewLocname,
convert(date,GETDATE()) as DateChanged

from d2l.D2L_Reporting_d2l_non_teach_prev

),

b (prevEmpid,
prevLastName,
prevFirstName,
prevD2LRole,
prevEmail,
prevLoc,
prevLocName,
DateChanged)

as
(select distinct IsNUll(empid,'') as prevEmpid,
IsNull(lastname,'') as prevLastName,
IsNull(firstname,'') as prevFirstName,
IsNull (d2l_Role,'') as prevD2LRole,
IsNull(email, '') as prevEmail,
IsNull(loc,'') as prevLoc,
IsNull(locname,'') as prevLocName,
convert(date,GETDATE()) as DateChanged
from d2l.D2L_Reporting_d2l_non_teach_prev
except
select distinct IsNUll(empid,'') as prevEmpid,
IsNull(lastname,'') as prevLastName,
IsNull(firstname,'') as prevFirstName,
IsNull (d2l_Role,'') as prevD2LRole,
IsNull(email, '') as prevEmail,
IsNull(loc,'') as prevLoc,
IsNull(locname,'') as prevLocName,
convert(date,GETDATE()) as DateChanged
from d2l.D2L_Reporting_d2l_non_teach

)
INSERT INTO [GSDR].[d2l].[D2L_Reporting_D2L_non_teach_delta]

select distinct a.newEmpid,
a.newLastName,
a.newFirstName,
a.newD2LRole,
a.newEmail,
a.newLoc,
a.NewLocname,
b.prevEmpid,
b.prevLastName,
b.prevFirstName,
b.prevD2LRole,
b.prevEmail,
b.prevLoc,
b.prevLocName,
case
when a.DateChanged IS NULL
Then b.DateChanged
when b.DateChanged IS Null
Then a.DateChanged
else ''
end as DateChanged,
case
when a.newEmpid IS Null
Then 'Deleted'
when b.prevEmpid IS Null
Then 'Addition'
Else 'Change'
End as Status

from a
full join b
on a.newEmpid = b.prevEmpid

order by a.newLastName

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Butterfly2

ASKER

Everytime I use a filter records that I need are removed.

Kent Olsen

Then we need to modify the filter.

Can you post what you've tried as the filter?

Butterfly2

ASKER

ok I played around with the filters and I think I got it.

This is what I used.
(a.newLoc <> b.prevLoc
or a.newD2LRole <> prevD2LRole
or a.newEmpid is null
or b.prevEmpid is null)

Kent Olsen

If that solves it, great!

Is the data likely to change so that a second row gets past the filter?