Jump to content

Comparing two tables > Have all the same rows?


Recommended Posts

I have two tables, table1 and table2.table1 has a field labelled "id".table2 has a field labelled "eid".I need to see if all of the eid values in table2 are contained in the id values for table1.Essentially I would like the most efficient method for this, possibly using a SELECT statement. Currently, I am getting all the eids in table2 and comparing each row with the entire table1 to see if there is a match between the eid and id.Cheers,Hobbs6

Link to post
Share on other sites

Well, sounds like you need to use a left/right join. Here is a sample query based on the information you gave. It should return a count to you of how many records in table 1 could not match to something in table 2.SELECT count(a.id) FROM table1 a RIGHT JOIN table2 bON a.id = b.eidWHERE a.id = NULL;

Link to post
Share on other sites
Well, sounds like you need to use a left/right join. Here is a sample query based on the information you gave. It should return a count to you of how many records in table 1 could not match to something in table 2.SELECT count(a.id)  FROM table1 a RIGHT JOIN table2 bON a.id = b.eidWHERE a.id = NULL;

What are the 'a' and 'b' in this code?
Link to post
Share on other sites

a and b are just names for the tables. In SQL you can rename tables so that they are shorter and easier to use elsewhere in the query.So without the a and b the query would have been:SELECT count(table1.id) FROM table1 RIGHT JOIN table2ON table1.id = table2.eidWHERE table1.id = NULL;So to rename the tables, you just type the current name, a space, then the new name. Then you can use the new name(s) throughout the rest of the query. This is especially helpful when you are working with joins on tables in different databases.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...