Jump to content

Comparing two tables > Have all the same rows?


hobbs6

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 comment
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 comment
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 comment
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 comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...