Link to home
Start Free TrialLog in
Avatar of 25112
25112

asked on

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

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)..
Avatar of 25112
25112

ASKER

if so, can that query be re-written more efficiently?
Avatar of 25112

ASKER

there are 2000 records in table1 but only 100 in table2
SOLUTION
Avatar of ggzfab
ggzfab

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of DBAduck - Ben Miller
DBAduck - Ben Miller
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
select * from table1 inner join table2 on table2.cvendno <> table1.cvendno and isnull(table1.cpaytono,'')<>''
@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 ?
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 25112

ASKER

super answers..

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