Answers

• allan Posts: 64,324Questions: 1Answers: 10,622 Site admin

  April 25

  I'm a little confused . You have a column in table A which references a column in table B, but some of the IDs that are referenced no longer exist in table B. Is that correct? That sounds like the referential integrity of the database is incomplete (perhaps it needs a foreign key constraint), but I would expect the join to resolve to null in such cases.

  Allan
• rf1234 Posts: 3,108Questions: 90Answers: 429

  April 25 edited April 25 Answer ✓

  In a list I want to make, I want to show data from the main table that does not have any ID filled in as a reference to any row in the secondary table.

  I interpret this that those main table entries may not have a corresponding entry in the secondary table.

  Then, what you want to achieve is this:

  SELECT a.field
  FROM main_table a
  LEFT JOIN secondary_table b ON a.id = b.main_table_id
  WHERE b.main_table_id IS NULL
  

  So you are doing a left join but you only want those rows from the main table that don't have a corresponding entry in the secondary table, i.e. the foreign key is null.

  In Editor this should look like this:

  Editor::inst( $db, 'main_table' )
  ....
  ->leftJoin( 'secondary_table', 'main_table.id', '=', 'secondary_table.main_table_id')
  ->where( 'secondary_table.main_table_id', null )
  
• danilorago Posts: 35Questions: 10Answers: 0

  April 25

  Hello friends,

  Sometimes I think that I explained very well, but this is dificult to me, hahaha, sorry!

  I have table A like below:

  ---

  | Table A

  | ID_TABLE_A
  | NAME

  | DESCRIPTION

  And table B like below:

  ---

  | Table B

  | ID_TABLE_B
  | ID_TABLE_A
  | NAME

  | DESCRIPTION

  Then what I need is that, on the list, shows only the lines from table A that don't have link to any line on table B on the field ID_TABLE_A

  For example, I have ID_TABLE_A as 1, 2, 3 and 4 on table A, then on table B I have two lines that is filled on column ID_TABLE_A as 2 and 3, then I need to show on the list only the ID 1 and 4, excluding the ID 2 and 3 because there's lines on table B "consuming" these IDs
• allan Posts: 64,324Questions: 1Answers: 10,622 Site admin

  April 25

  Ah yes, sorry. I missed that point and that it would need a filter to achieve it. Thanks @rf1234!

  Allan
• danilorago Posts: 35Questions: 10Answers: 0

  April 25

  Thanks @rf1234, it's working