April 6, 2005 at 7:43 am
This is something simple but I just cant get the sql down.
Im trying to get only records that do not exist in another table. CAn anyone Help. Thanks
SELECT [4gl Datamart].dbo.tmp_D_Customer.cust_sys_num, [4gl Datamart].dbo.tmp_D_Customer.cust_num, [4gl Datamart].dbo.tmp_D_Customer.cust_name,
[4gl Datamart].dbo.tmp_D_Customer.cust_territory, [4gl Datamart].dbo.tmp_D_Customer.credit_limit
FROM [4gl Datamart].dbo.tmp_D_Customer LEFT OUTER JOIN
[4gl Datamart].dbo.test_D_Customer ON [4gl Datamart].dbo.tmp_D_Customer.cust_sys_num <> [4gl Datamart].dbo.test_D_Customer.cust_sys_num
April 6, 2005 at 7:48 am
just change this :
ON [4gl Datamart].dbo.tmp_D_Customer.cust_sys_num = [4gl Datamart].dbo.test_D_Customer.cust_sys_num
April 6, 2005 at 8:34 am
You can try this:
SELECT [4gl Datamart].dbo.tmp_D_Customer.cust_sys_num, [4gl Datamart].dbo.tmp_D_Customer.cust_num, [4gl Datamart].dbo.tmp_D_Customer.cust_name,
[4gl Datamart].dbo.tmp_D_Customer.cust_territory, [4gl Datamart].dbo.tmp_D_Customer.credit_limit
FROM [4gl Datamart].dbo.tmp_D_Customer
WHERE NOT EXISTS (SELECT 'X'
FROM [4gl Datamart].dbo.test_D_Customer
WHERE [4gl Datamart].dbo.tmp_D_Customer.cust_sys_num = [4gl Datamart].dbo.test_D_Customer.cust_sys_num)
April 7, 2005 at 1:33 am
Remi gave you almost the right answer (replace <> with = in the LEFT JOIN), just forgot to mention the second part of it - that is, if you want to select records that are NOT included in the table "test_D_Customer", you need to add WHERE clause like that:
WHERE [4gl Datamart].dbo.test_D_Customer.cust_sys_num IS NULL
LEFT JOIN with = provides that all rows from the first table will be returned, and if there is corresponding row in the second table, the column on which these tables are joined will surely have some value. If the column used to join returns NULL from the second table, it means that no matching rows were found.
AFAIK this should perform better than NOT EXISTS, but you can test both and see what you like best. BTW, when using (NOT) EXISTS, I always use SELECT * (not SELECT 'X'), but maybe it's just a habit and has no real significance - I don't know.
April 7, 2005 at 6:58 am
I already tested that theory, and there's no speed difference between select * and select 'x' and an exists statement. The function just returns true without trying to figure out the columns to return, therefore fetching no data.
April 7, 2005 at 9:58 am
Yep,
"EXISTS" does not look at the field list at all it only check the "JOIN"s and the "WHERE" clauses
Cheers!
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply