asked on

Combine three tables

Hello:

I have three tables that look like this:

TableA

PersonNane
IDCodeAbbv
WorkID
Location
WorkDate

TableB
IDCodeAbbv
IDCodeName

TableC
WorkID
WorkLocation

In TableA if IDCodeAbbv is not null than WorkID will not be in TableC but IDCodeAbbv will be in TableB. If WorkID is not null than WorkID will be in TableC. At no time, will either IDCodeAbbv or WorkID be null at the same time.

This is an example of how the output should look like

PersonName WorkID WorkLocation IDCodeAbbv IDCodeName
Dan 2 Base 1
Tom SR South Rear
John 4 Base 2
Sam NE North Entrance

ASKER CERTIFIED SOLUTION

COANetwork

membership

This solution is only available to members.

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

Start Free Trial

SOLUTION

didnthaveaname

membership

This solution is only available to members.

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

Start Free Trial

RecipeDan

ASKER

Sorry about this...I did not create the database, it was handed to me.

After giviing the data a closer look. In TableA if IDCodeAbbv is not null than WorkID will not be in TableC but IDCodeAbbv will be in TableB. However, when IDCodeAbbv is not null WorkID is given a value of -1

COANetwork

it does not matter, as long as there is no corresponding WorkID = -1 in TableC. Both mine and didnthaveaname's sampels will work - mine will produce NULL where there is no data, his will produce an empty string. The actual values of your keys do not matter, what matters is the presence or absence of SAME values in the corresponding tables.

RecipeDan

ASKER

Thank you both for your help