Hi, I have two tables. Main table is soxbillingrelationship having a column as BRID along with many other columns. The other table is BR having just two columns one is BRID and other is OMS. Now i have to extract all the rows from the table BR which has BRID not existing in Soxbillingrelationship. I tried this query: select * from BR where BRID NOT IN (select brid from SoxBillingRelationship) order by BRID This is returning around 204 rows whereas totally there are more than 1000 BRID which are not in soxbillingrelationship table. Thanks for the advice!!
Perhaps this gives you the expected result, minus 204: SELECT COUNT(*) FROM BR WHERE BRID IS NULL ... meaning that your BR table has a NULL on many rows. Rows with a null on the BRID column are being ignored in your syntax.
I am sure that there are no nulls as i have only created the other table with some set of records in it and that has no nulls. Even the query given above gives me the same number of rows i.e. 204.
Is it a fixed-width type, like CHAR? Post some sample values from BRID on the BR table, that you would expect to be NOT IN the other column. Also, run the query with IN (instead of NOT IN) and see if those BRID values from the BR table are now listed yes/no.
Try something like : select * from BR where ISNULL(BRID,-1) NOT IN (select brid from SoxBillingRelationship) order by BRID
Hi, How about use the NOT EXISTS command instead to see if you get the correct results. SELECT * FROM BR WHERE NOT EXISTS ( SELECT 1 FROM SoxBillingRelationship AS s WHERE s.BRID = BR.BRID ) Regards Richard...
hope this helps. SELECT * FROM BR one LEFT OUTER JOIN SoxBillingRelationship two on one.BRID = two.brid WHERE two.brid IS NULL