We help IT Professionals succeed at work.

query help - if dependent record is in another table..

25112
25112 used Ask the Experts™
on
there are 2 tables..

IF there is something in the table1.cpaytono  field, a reference record will be in the table2 table.

There may be multiple records in the table2 table for each record in the table1 table.

Does the below query prove that every cpaytono in table1 has a record in table2:

select * from table1 where cpaytono <> ''  and cpaytono not in (select caddrno from table2 where cvendno = table1.cvendno)..
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
if so, can that query be re-written more efficiently?

Author

Commented:
there are 2000 records in table1 but only 100 in table2
Commented:
First check or NULL's are allowed for the cpaytono field, as the <> '' won't work for them.

Looks to me that the cpaytono is mandatory when a table2 record is presen. From the query I concluded that you can JOIN the tables using cpaytono and caddrno

To find out or a table2 record is missing you could use an OUTER JOIN (using cpaytono and caddrno) and test for the caddrno field to be NULL to find missing reference records.
Commented:
I think you want to change your "NOT IN" to "IN" in your query.  You want the cases where the cpaytono column has a value and that it exists in the other table.  Right now you're producing all the rows where it has a value but doesn't exist in the other table.

You can also add a cpaytono is not null to handle that case in your query...

select * from table1 
    where cpaytono <> ''  AND 
        cpaytono is not null AND 
        cpaytono in 
            (select caddrno from table2 where cvendno = table1.cvendno)

Open in new window

Commented:
SELECT table1.*
FROM table1
INNER JOIN table2 on table1.cvendno = table2.cvendno AND table1.cpaytono = table2.caddrno
WHERE table1.cpaytono <> ''
AND table1.cpaytono IS NOT NULL
Commented:
That will produce potentially multiple rows of output per row of table1, the original query will produce 1 row from table1 where there are 1 or more records matching in table2.
Commented:
Then I would use Exists instead of IN.
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Your query

select * from table1 where cpaytono <> ''  and cpaytono not in (select caddrno from table2 where cvendno = table1.cvendno)

Would in fact prove that every table1.cpaytono had an entry in table2 (caddrno) for the same cvendno if and only if it return no results.

So, you end up selecting nothing.

That query could be written more efficiently, just depends on what you are needing to actually report.

For a start, cpaytono <> '' does not exclude NULL values, maybe caddrno cannot be NULL so maybe thats OK. If it can be null, then there is little bit more to your problem.

It also depends a bit on your indexes as to efficiency and speed.

I might be inclinedd to start with :

select * 
from  table1 T1
left outer join table2 T2 on T2.cvendno = T1.cvendno and T2.caddrno = T1.cpaytono
where  isnull(cpaytono,'') <> ''   
and    T2.cvendno is NULL

Open in new window


Assuming table2 has an index with cvendno and caddrno then it should be pretty quick.

The left outer join means it is optional to exist in table2

Testing for a column in table2 (used in the join) for NULL means there was no corresponding entry.

The other option for this type of thing is the EXISTS option (as mentioned above by dbaduck)

select * 
from  table1 T1
where not exists (select null from table2 T2 where T2.cvendno = T1.cvendno and T2.caddrno = T1.cpaytono)
and  isnull(cpaytono,'') <> ''   

Open in new window


You can see that there is some similarity. We select NULL because it doesnt matter if anything is returnedd or not, we simply check to see if it exists.

Now, you might not even need the isnull(cpaytono,'') <> '' depending on what you are trying to report on.

Maybe if you can describe what you hope to get out of the query, then we could help a bit more.

Commented:
select * from table1 inner join table2 on table2.cvendno <> table1.cvendno and isnull(table1.cpaytono,'')<>''
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
@keyu,

Think for a moment about table1 having cvendno = 1,2,3,4 then table2 having cvendno 1,2,3,4
What you will end up with is table1 cvendno = 1 linked to table2 cvendno = 2,3,4 and table1 cvendo = 2 linked to table2 cvendno -= 1,3,4 etc...  Where in reality table1 and table2 have the same cvendno's, and where is the comparison of cpaytono to  caddrno ?

So, think your query is not answering the question whatsoever. Or am I missing something ?

Commented:
You want [proof that for each record in table 1 you have respected records in table 2

right ???????

cvendno  has datatype int i guess so there will be no chance we get it in comma seperated form as you are jpoining both tables using that "cvendno"

Now with my this qury

select * from table1 inner join table2 on table2.cvendno <> table1.cvendno and isnull(table1.cpaytono,'')<>''

1) it will remove all null vales from the result

2) it will find those recoeds of table1 which are not exist in table 2 so you have a list of
"cvendno" from table1 which are not available in table2
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Didnt mean them to look like comma seperated, just the different possible values.

And just trying to help out a bit... The comment in point 2 is not quite correct, and thought you might be thinking about something else.

Have a look at the code below :) It clearly shows entries that are in table2.

-- create some test tables and test data 

use tempdb
go

-- first check to see if we have already used them and get rid of test tables.

if object_id('#ee_table1','U') is not null drop table #ee_table1
if object_id('#ee_table2','U') is not null drop table #ee_table2
go

-- now we can create some new test tables (# = temp tables) and populate with dummy data

create table #ee_table1 (cvendno int, cpaytono int)
insert #ee_table1
select 1,1    union all
select 2,1    union all
select 3,1    union all
select 4,1    union all
select 5,1    union all
select 6,null union all
select 7,7    union all
select 8,null union all
select 9,9   
go


create table #ee_table2 (cvendno int, caddrno int)
insert #ee_table2
select 1,1    union all
select 2,2    union all
select 3,1    union all
select 4,1    union all
select 8,8    union all
select 9,9   
go

-- now lets run that query of yours using the test tables and test data above

select * from #ee_table1 inner join #ee_table2 on #ee_table2.cvendno <> #ee_table1.cvendno and isnull(#ee_table1.cpaytono,'')<>''

/* results
cvendno cpaytono cvendno caddrno
1       1        2       2
1       1        3       1
1       1        4       1
1       1        8       8
1       1        9       9
2       1        1       1
2       1        3       1
2       1        4       1
2       1        8       8
2       1        9       9
3       1        1       1
3       1        2       2
3       1        4       1
3       1        8       8
3       1        9       9
4       1        1       1
4       1        2       2
4       1        3       1
4       1        8       8
4       1        9       9
5       1        1       1
5       1        2       2
5       1        3       1
5       1        4       1
5       1        8       8
5       1        9       9
7       7        1       1
7       7        2       2
7       7        3       1
7       7        4       1
7       7        8       8
7       7        9       9
9       9        1       1
9       9        2       2
9       9        3       1
9       9        4       1
9       9        8       8
*/ 

-- now clean up

Open in new window


Hope that clears it up for you...

Now, back to the real question, and now we have some test data, lets also have a look at the other queries I posted before to compare the results with the above (using the same test tables, so, dont drop just yet...

-- using the outer join and reporting where table2 column is NULL (ie not matched in join)

select T1.* 
from  #ee_table1 T1
left outer join #ee_table2 T2 on T2.cvendno = T1.cvendno and T2.caddrno = T1.cpaytono
where  isnull(cpaytono,'') <> ''   
and    T2.cvendno is NULL

-- or using not exists

select * 
from  #ee_table1 T1
where not exists (select null from #ee_table2 T2 where T2.cvendno = T1.cvendno and T2.caddrno = T1.cpaytono)
and  isnull(cpaytono,'') <> ''   

/* both return the same results as the original query posted
cvendno cpaytono
2       1
5       1
7       7
*/

-- BUT, there are other rows in #ee-table1 that arent in #ee_table2 
-- and to show those we simply comment out "and  isnull(cpaytono,'') <> '' "

select * 
from  #ee_table1 T1
where not exists (select null from #ee_table2 T2 where T2.cvendno = T1.cvendno and T2.caddrno = T1.cpaytono)

-- and we get the two additional rows in table1 where the cpaytono is null.

Open in new window


and now do the last little bit of cleanup

if object_id('#ee_table1','U') is not null drop table #ee_table1
if object_id('#ee_table2','U') is not null drop table #ee_table2
go

Open in new window


Hope that clears up my earlier comment...

Author

Commented:
super answers..

Mark, much appreciated your article type answer- detailed and precise..