52445

How to check if every primary key value is being referenced as foreign key in another table

I have two table tb1 and tb2 . id in tb1 is pk and referenced as fk in tb2. I want to know if tb1 has id values as 1,2,3,4,5 and tb2 has fk_values for 1,2,3,4 but doesn't have for pk 5 how can i find this.

tb1 ------ id ------ 1 ------ 2 ------ 3 ------ 4 ------ while tb2 fk_id ------- 1 -- 1 -- 2 -- 3 -- 3 --

but table 2 doesn't have values for 4 then how can i find out the 4 value here .

Database to be used is mysql.

Answer1:

To find what's in tb1 but not in tb2 do this:

SELECT tb1.* FROM tb1 LEFT JOIN tb2 ON tb1.id = tb2.fk_id WHERE tb2.fk_id IS NULL

To do it the other way (in tb2 but not in tb1 ), which cannot be in this case because, there is a foreign key, but none the less it might be useful for you later simply switch the two tables

SELECT tb2.* FROM tb2 LEFT JOIN tb1 ON tb1.id = tb2.fk_id WHERE tb1.id IS NULL

Answer2:

use the left join .left join will give you the result that only exists in the left table.which means you must use tb1 left join tb2 like this.

// pick the tb1.id in the result when the fk_id is null. select tb1.id from tb1 left join tb2 on tb1.id = tb2.fk_id where tb2.fk_id is null;

Recommend

  • javascript multiple input textbox validation
  • How do I remove duplicates in paging
  • Mysql - Summing counts of multiple joined tables
  • complex query join checking that value does not exist
  • PDO error when wrong host name
  • MYSQ & MVC3 SQL connection error \\ ProviderManifestToken but I am using MySQL
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • Moving mysql files across servers
  • Listbox within Listbox and scrolling trouble in Windows Phone 7 Silverlight
  • Disable Enter in editText android
  • Jetty Server not starting: Unable to establish loopback connection
  • Django: Count of Group Elements
  • Recording logins for password protected directories
  • Using $this when not in object context
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • Deselecting radio buttons while keeping the View Model in synch
  • Getting last autonumber in access
  • Counter field in MS Access, how to generate?
  • Incrementing object id automatically JS constructor (static method and variable)
  • How to extract text from Word files using C#?
  • Sending data from AppleScript to FileMaker records
  • How to handle AllServersUnavailable Exception
  • Can I have the cursor start on a particular column by default in jqgrid's edit mode?
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • jquery mobile loadPage not working
  • How to get next/previous record number?
  • Comma separated Values
  • How to delete a row from a dynamic generate table using jquery?
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • python regex in pyparsing
  • How do you join a server to an Active Directory (domain)?
  • How to stop GridView from loading again when I press back button?
  • How does Linux kernel interrupt the application?
  • How can I remove ASP.NET Designer.cs files?
  • python draw pie shapes with colour filled
  • Is there any way to bind data to data.frame by some index?
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can i traverse a binary tree from right to left in java?