January 14, 2008 at 12:01 pm
Hi all. I'm trying to do some data verification on some existing data. I want to make sure that the data in Table A exists in Table B based on a client id. My problem is that Table B can have multiple rows.
For Example:
Table A
ID Value Client
1 3 A10
2 2 A10
3 3 A10
4 6 A10
Table B
ID Value Client
1 2 A10
2 3 A10
Since there is no value of 6 in Table B, I want this to show as the only result and display the id of 4 so I can reference it. My join on Client doesn't seem to be doing the trick and I think I need to see where my value is not in the other table, but I can't figure out how to do this for multiple clients at one time.
January 14, 2008 at 12:15 pm
If would be easier to help you with your query if you posted the code for it.
January 14, 2008 at 12:21 pm
You can try this query, I am not sure it will work.
SELECT a.value, b.bvalue
FROM TableA a
LEFT OUTER JOIN (SELECT DISTINCT Value bvalue
FROM TableB) b ON a.value = b.bvalue
WHERE b.bvalue IS NULL
January 14, 2008 at 12:40 pm
Thanks. Pointing me to the Outer join did it, but I needed to join on two fields, my client and my value.
Here's what I ended up doing:
select tablea.ID, tablea.client, tablea.value, tableb.value
from tablea
full outer join (select client, pgmkey from tableb) temptable
on tablea.value = temptable.value
and tablea.client = temptable.client
where tableb.value is null and tablea.ID is not null
order by tablea.client
January 15, 2008 at 10:38 am
Wait a minute, I thought you wanted to find out which data in TableA but not TableB and there were multiple rows in TableB.
Full outer join means you want to get data in TableB but not in TableA and data in TableA not in TableB.
The where clause of the query will return the data in TableA but not in TableB, in this case a LEFT JOIN should be enough.
You need to do a distinct in TableB to eliminate multiple rows in TableB and also I corrected a few things.
select tablea.ID, tablea.client, tablea.value, temptable.value
from tablea
full outer join (select DISTINCT client, value, pgmkey from tableb) temptable
on tablea.value = temptable.value
and tablea.client = temptable.client
where temptable.value is null AND tablea.ID is not null
order by tablea.client
January 16, 2008 at 12:58 pm
use EXCEPT.
select * from a
except
select * from b
result will distinct rows in A that are not in B.
You can also add where clauses since you mentioned something about "based on Client ID"
Returns distinct values by comparing the results of two queries.
EXCEPT returns any distinct values from the left query that are not also found on the right query.
INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.
The basic rules for combining the result sets of two queries that use EXCEPT or INTERSECT are the following:
The number and the order of the columns must be the same in all queries.
The data types must be compatible.
Transact-SQL Syntax Conventions
Syntax
{ ) }
{ EXCEPT | INTERSECT }
{ ) }
January 16, 2008 at 1:09 pm
Bob, EXCEPT and INTERSECT won't work here (if the post is in the correct forum) as this is SQL Server 7, 2000. EXCEPT and INTERSECT are new to SQL Server 2005.
😎
January 16, 2008 at 1:10 pm
K Currie, You should read this article: http://www.sqlservercentral.com/articles/Best+Practices/61537/. It will help when asking for help as people can test their suggestions prior to posting them.
😎
January 16, 2008 at 2:15 pm
Thanks. I realized that after I posted it, but honestly had to get some work done, and forget to get back to it. Just did a small presentation here on rarely used (but useful) SQL commands.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply