writing a query to find table entries that have no records in related table
-
I have a simple problem with a not-so-simple query (for me) to write. I have table A with has a single related table B. The relationship between A and B is a 1 : many I need a query the selects all rows in A that have nothing in B. I tried doing a query using Count(1) from B but I just cannot figure out the correct syntax to get a working query. Appreciate it if the SQL Gurus can help. Thanks
-
I have a simple problem with a not-so-simple query (for me) to write. I have table A with has a single related table B. The relationship between A and B is a 1 : many I need a query the selects all rows in A that have nothing in B. I tried doing a query using Count(1) from B but I just cannot figure out the correct syntax to get a working query. Appreciate it if the SQL Gurus can help. Thanks
Not sure exactly what you're trying to do but perhaps something of this form could help? SELECT * FROM A WHERE NOT EXISTS (SELECT * FROM B WHERE A.x=B.y)
CQ de W5ALT
Walt Fair, Jr., P. E. Comport Computing Specializing in Technical Engineering Software
-
Not sure exactly what you're trying to do but perhaps something of this form could help? SELECT * FROM A WHERE NOT EXISTS (SELECT * FROM B WHERE A.x=B.y)
CQ de W5ALT
Walt Fair, Jr., P. E. Comport Computing Specializing in Technical Engineering Software
Your query did the job. Thank you. As to "what I'm trying to do" I have one customer group creating the entries in table A. I have a second ops group out on the floor that creates the entries in table B. The second group needs to know all setups that have not been setup on the floor. Thus the query.
-
Your query did the job. Thank you. As to "what I'm trying to do" I have one customer group creating the entries in table A. I have a second ops group out on the floor that creates the entries in table B. The second group needs to know all setups that have not been setup on the floor. Thus the query.
Glad it worked. I've done similar queries to find out where data, etc. is missing.
CQ de W5ALT
Walt Fair, Jr., P. E. Comport Computing Specializing in Technical Engineering Software
-
I have a simple problem with a not-so-simple query (for me) to write. I have table A with has a single related table B. The relationship between A and B is a 1 : many I need a query the selects all rows in A that have nothing in B. I tried doing a query using Count(1) from B but I just cannot figure out the correct syntax to get a working query. Appreciate it if the SQL Gurus can help. Thanks
-
I have a simple problem with a not-so-simple query (for me) to write. I have table A with has a single related table B. The relationship between A and B is a 1 : many I need a query the selects all rows in A that have nothing in B. I tried doing a query using Count(1) from B but I just cannot figure out the correct syntax to get a working query. Appreciate it if the SQL Gurus can help. Thanks
You might also want to look at an outer join. I wrote something about this a while back: http://www.citrustechnology.com/blog/20100113