[Resolved] SELECT unused Reference Table Records

[jcarver]

Hi,

I have a reference table (basically a list of IDs and varchars). I would like to select out all the references that are NOT linked up. I'm trying to do this in a single MySQL call.

In a last-ditch effort I've tried things like:
SELECT ReferenceName FROM ReferenceTable, LinkTable WHERE ReferenceTable.ID != LinkTable.ID

But of course that didn't work.

Any suggestions?

Thanks,
Carver

[barand]

You need to use a left join

SELECT R.ID FROM ReferenceTable R LEFT JOIN LinkTable L ON R.ID = L.ID WHERE L.ID is null

This takes all records from Ref but puts nulls where the there is no matching Link record. The where clause gives just the unmatched id's.

[jcarver]

Thanks for the help! Unfortunately I'm still having problems...

When I look at the result to the query you suggested, it appears that the records without a match are not displaying.

How I tried to handle the error:

• I switched up the Join Table order (no change)

• I pulled out the WHERE clause completely, to look for NULL values (found none, but I'm sure there are records that aren't linked)

• I did a distinctrow select on the IDs and found that more IDs are in the table than show up in the query (without the WHERE clause)

I'm doing this query through PHP MyAdmin:
SELECT DISTINCTROW L.Company_ID
FROM Contact_Company_Link L
LEFT JOIN Companies C ON C.Company_ID = L.Company_ID

The number of rows returned is less than the rows in the Companies table, which would suggest it's not working as expected.

Thanks in advance

[jcarver]

Okay, it seems to be working this morning. I'm not sure what was wrong last night. Coding at 4am seems to be a bad idea...

Thanks!