0

I need to find the products which has invalid foreign key assigned.

Note :

  • Company has many products
  • Company has many categories
  • Products has one category (foreign key = category_id)
SELECT *
FROM products
WHERE category_id NOT IN (SELECT id FROM categories);

will not help me because a company level check is required. Ie,

  • Company A

    • has Categories c1, c2
    • has Product P1 belongs to c3 (invalid), Product P2 belongs to c1 (valid)

  • Company B

    • has category c1,c2, c3

So in the above scenario I need to find Product P1 which refers to category c3 which is not available at p1's company (Company A).

Crazy Cat
  • 186
  • 3
  • 13
  • 2
    Make it easy, and possible, to assist you: [mcve]. – jarlh Jan 04 '21 at 14:32
  • Potential duplicate of https://stackoverflow.com/questions/4076098/how-to-select-rows-with-no-matching-entry-in-another-table although there are more likely better Rails specific dup targets. – max Jan 04 '21 at 14:40
  • 1
    @CrazyCat . . . You don't even show the foreign key (or intended foreign key) definition. The question is quite unclear. – Gordon Linoff Jan 04 '21 at 14:52
  • category_id is the foreign_key to category table @GordonLinoff – Crazy Cat Jan 04 '21 at 14:55
  • You can do an OUTER JOIN and look for null values in the join field. – Ben Trewern Jan 04 '21 at 15:40
  • 1
    Without the schema for the tables involved this is not going to get you a solid answer. For one there is no indication of how to determine what are legitimate categories for a company. – Adrian Klaver Jan 04 '21 at 16:08
  • 1
    If `category_id` is foreign key then it's not possible to have non-matching values with it. The engine won't allow them. The question doesn't make too much sense. – The Impaler Jan 04 '21 at 16:15
  • @TheImpaler in Rails its unfortunely quite common that devs incorrectly refer to any column containing references to another table as a "foreign key" no matter if there is a foreign key constraint or not actually linking them. Its partly ActiveRecord's fault as it plays loose and fast with the terminology. – max Jan 04 '21 at 18:36
  • @max Thanks for the clarification. I'll keep it in mind. – The Impaler Jan 04 '21 at 21:58

0 Answers0