One to many query for unmatched data

[jjz0512]

I have tow tables, Compensation and Site, that have a primary key that match. The site table has multiple rows for 1 record in the compensation table. I trying to see if a record in the compensation table where the site id's don't match what's in the Site table.

For Example:
In the Compensation Table, CompID = 1 and CompSiteID = 62.
In the Site Table I have a number of records where CompID = 1 and SiteID ='s 11, 12, 17, 56, and 72. But no 62.

I need to be able to collect those records that don't match.

 

[Beetle]

If this is a one to many relationship, and Compensation is the parent table, why are you storing SiteID in Compensation to begin with?

Can you elaborate a bit more on your table structure?

 

[jjz0512]

It an old design and needs to limb along a little longer. From the Compensation table; who's being paid now and what site they are currently at. The Site table; contains the current and past sites they have been compensated for.

So, I need to find the current Compensation record that doesn't have a corresponding Site record.

 

[Beetle]

You would use a query like the following;

SELECT CompSiteID
FROM Compensation
LEFT JOIN Site ON Compensation.CompSiteID = Site.SiteID
WHERE Site.SiteID Is Null

 

[jjz0512]

Bettle, thanks for the reply. Thought I tried something like that earlier, but tried yours. It returns all the record that DON'T have a SITE_ID in the SITE Table. I only need to see somebody that in the Compensation table that doesn't have the CompSite found in the Compensation table that NOT in the Site Table. I really need to see where an ID has a SITE, that not listed in the SITE table.

 

[Beetle]

It returns all the record that DON'T have a SITE_ID in the SITE Table.

I thought that's what you wanted

I really need to see where an ID has a SITE, that not listed in the SITE table.

Again, what you seem to be asking for here sounds the same as what you said it returned in the first quote above.

I'm not really clear on what results you are expecting.

 

[jjz0512]

Beetle, it just about there. After looking at you query a little more. I like that it give me a list of people that have a compensation record with a Site ID, but no record in the SiteWorked Table for that compensation record.

But what I also need is; I have record CompensationTable, CompID of 203 with a SiteID of 62. When I look in the SiteWorked Table I see no record for CompID 203 with a SiteID of 62. Only other Sites he works at. The SiteWorked Table isn't a lookup table, is a historical table of all the Sites CompID 203 worked at.

Example:
CompensationTable SiteTable
CompID 203, SiteID 62 CompID 203, SiteID 13
CompID 203, SiteID 14
CompID 203, SiteID 72

The Compensation Table is Current
The Site Table is a row for every site that he work at.

I shouldn't see a current record in the Compensation Table with a SiteID without a record in the SiteWorked Table SiteID column.

I hope this makes a little clearer.

 

[Beetle]

If you need to see records in the Site table that have no related record in Compensation then you would just reverse the logic of the query;

SELECT SiteID
FROM Site
LEFT JOIN Compensation ON Site.SiteID = Compensation.CompSiteID
WHERE Compensation.CompSiteID Is Null

 

[jjz0512]

No, I want the records from the Compensation table that don't match a row in the Site table. I added another table to limit to only current employees, but I'm still getting this;

COMP_ID SITE_ID EMPLOYED
173 5 yes
203 62 yes
219 69 yes

I should only see 203 in the example. Could it be because of the 1 (Compensation record) to many(Site records) relationship?

:banghead:

 

[Beetle]

Can you upload a small database with some sample data that demonstrates the problem so I can see what you've got going on?