April 9, 2007 at 4:42 pm
It's been a long while since I've had to help a developer with compound keys, so I've forgotten this. I know some of you know this by heart! Help please?
PKTable and FKTable
SELECT * out of PKTable where FK doesn't exists in FKtable.
Since if it's only one column, not so simple when there are more than one column. I tried a derived table with JOIN PKKey1 = FKKey1 AND PKKey2 = FKKey2, but that isn't working either, so I'm sure I'm just missing something. Anyone have enough brain left tonight to help me?
Thanks,
Lezza
April 9, 2007 at 5:04 pm
If I understand you correctly, and you're trying to find records in PK that don't have a corresponding record in FK, based on the FK value in the PK table (whew, that's a mouthful), what about:
SELECT
p.*
FROM
PKTable AS p
LEFT OUT JOIN FKTable AS f ON
p.FKColumn = f.FKColumn
AND p.AnotherFKColumnIfNeeded = f.AnotherFKColumnIfNeeded
WHERE
f.FKColumn IS Null
AND f.AnotherFKColumnIfNeeded IS Null
You'll need to join on whatever columns are necessary to uniquely identify an FKTable row, so change the code as needed. Also, if there is a column in the FK table that is NOT NULL, you can simplify the WHERE clause to just check that one for null. In other words, what it's doing is pulling in all rows from the PKTable, joining them where they match to the FKTable, and stripping out any that don't have a match.
If I'm not understanding you correctly, how about some DDL, and more info about exactly what you're trying to accomplish.
April 10, 2007 at 8:18 am
Thank you David! I tried that solution on my own yesterdat, and it didn't give me the right results set, but when I cut and pasted your code in (with my object substitutions) it worked brilliantly. As I stated, I just couldn't wrap my brain around this yesterday. Thank you SO much.
Lezza
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply