Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

asked on

Comparison query - 4 columns

Hi Experts,

I have two identical tables both have 4 columns, lets say Column1,  Col2, Col3 and Col4.

Now I need to get all records that exists in table1 and not in table2 by comparing all 4 columns, what is the best way of doing that?
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

ASKER

Hi,

I tried yours and didnt work

select a.*, b.* from
Timesheet1 a right join Timesheet2 b
on a.[Resource Name] = b.[Resource Name] and a.[Shift Segment Start] = b.[Shift Segment Start] and a.[Shift Segment End] = b.[Shift Segment End] and a.[Date Worked] = b.[Date Worked]
where a.[Resource Name] is null

Open in new window


Thanks,
Ben
can you provide sample data with their data types?
Avatar of bfuchs

ASKER

See attached.

both files are actually excel files linked into access, in addition they have additional columns, however I don't think all these matters here..

Thanks,
Ben
Timesheet1.xls
I tried yours and didnt work
I'm using left join while in your example above you're using right join ...
Avatar of bfuchs

ASKER

I know that, but I need to see all records from table2 and therefore as you can see I also modified the last line accordingly
where a.[Resource Name] is null

Open in new window

Correct me if I'm wrong..

Thanks,
Ben
Avatar of bfuchs

ASKER

Pardon, It does seem to work, I guess was looking at first few columns that were empty.

Thanks,
Ben
Avatar of bfuchs

ASKER

Thank You!
It does seem to work, I guess was looking at first few columns that were empty
since you're using a left join and comparing with 4 columns in the condition to check for the existence. you can use any of these columns in the "Where" condition, such as:

where a.[ any of these 4 columns] is null

Open in new window