December 11, 2009 at 1:56 pm
Hi!
I have two tables, tableA(DealerId, ISCI)
tableB (DealerId, ISCI)
Sample data:
TableA
DealerID ISCI
s021 1234
s021 2354
s021 2564
TableB
DealerID ISCI
s021 1234
Now, I would need to find how many Dealers with ISCI from TableA is not in TableB. With the example above, the result should be two (s021, 2354 And s021, 2564).
How would I write a sql statement to get this result. I hope my question is clear. Please let me know if you need further clarification.
Thanks in advance.
December 11, 2009 at 2:24 pm
This is a simple LEFT OUTER JOIN:
DECLARE @tableA TABLE (DealerId varchar(10), ISCI varchar(10))
DECLARE @tableB TABLE (DealerId varchar(10), ISCI varchar(10))
INSERT INTO @tableA
SELECT 's021','1234' UNION ALL
SELECT 's021','2354' UNION ALL
SELECT 's021','2564'
INSERT INTO @TableB
SELECT 'DealerID','ISCI' UNION ALL
SELECT 's021','1234'
SELECT a.*
FROM @tableA a
LEFT JOIN @tableB b
ON a.ISCI = b.ISCI
WHERE b.ISCI IS NULL
December 11, 2009 at 4:45 pm
I am sorry not to provide you enough samples. In most cases, multiple dealers have the same ISCI codes.
Your example:
DECLARE @tableA TABLE (DealerId varchar(10), ISCI varchar(10))
DECLARE @tableB TABLE (DealerId varchar(10), ISCI varchar(10))
INSERT INTO @tableA
SELECT 's021','1234' UNION ALL
SELECT 's021','2354' UNION ALL
SELECT 's021','2564' UNION ALL
SELECT 's022', '2354 UNION ALL
SELECT 's022', '5985' UNION ALL
SELECT 's023', '1234' UNION ALL
INSERT INTO @TableB
SELECT 'DealerID','ISCI' UNION ALL
SELECT 's021','1234' UNION ALL
SELECT 's022', '5985' UNION ALL
SELECT 's023', '1234'
The table consists of DealerId and ISCI columns as primary keys to make the row unique. I should have mentioned this earlier. Will the query below still work?
SELECT a.*
FROM @tableA a
LEFT JOIN @tableB b
ON a.ISCI = b.ISCI
WHERE b.ISCI IS NULL
December 14, 2009 at 11:42 am
Given the new sample data that you've shown, what would you expect the results to look like?
December 14, 2009 at 2:48 pm
Sorry to confuse you but I would need to update tableB with data from tableA thats not in tableB.
Since, in this case, a dealerid and ISCI fields make a row unique, I didn't know how to write something like
SELECT *
FROM tableA
WHERE dealerid NOT IN (SELECT dealerid FROM tableB)
AND ISCI NOT IN (SELECT ISCI FROM tableB)
I am trying to write a statement similar to above. I know query above is not right. Let me know if I make this more clear.
Thanks!
December 14, 2009 at 2:52 pm
This is still a simple LEFT OUTER JOIN, you just need to change the JOIN criteria to include the whole primary key.
SELECT a.*
FROM @tableA a
LEFT JOIN @tableB b
ON a.dealerid = b.dealerid AND a.ISCI = b.ISCI
WHERE b.dealerid IS NULL AND b.ISCI IS NULL
December 14, 2009 at 3:12 pm
Now I get it. Thanks a lot for your help on this.
December 14, 2009 at 3:14 pm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply