hobbs6 Posted November 8, 2005 Share Posted November 8, 2005 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 More sharing options...
Kcarson Posted November 8, 2005 Share Posted November 8, 2005 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 More sharing options...
hobbs6 Posted November 8, 2005 Author Share Posted November 8, 2005 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;<{POST_SNAPBACK}> What are the 'a' and 'b' in this code? Link to comment Share on other sites More sharing options...
Kcarson Posted November 8, 2005 Share Posted November 8, 2005 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now