July 25, 2005 at 1:40 pm
can u help me compare the data in 2 different tables. they both have the same column name and i want to get the ones from the first table that dont' match any from the ones in the 2nd table. thanks
July 25, 2005 at 1:46 pm
Take a look at the different join types in BOL. In your case look at the outer joins section.
July 25, 2005 at 1:59 pm
i dont' think i want to join the tables....i want to create a new table that lists the ones from table 1 that dont' match table 2
July 25, 2005 at 2:15 pm
That is exactly what a join is.
-- Select All items from Table1 where there is no Equal value for column1 in table2
Select *
from Table1
LEFT OUTER JOIN Table2 on Table1.Column1 = Table2.Column1
where Table2.Column1 is NULL
Edit: From Books Online
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_09_3mk3.asp
July 25, 2005 at 2:22 pm
Or from today's article of 2003 :
July 26, 2005 at 6:53 am
I have also found that using a UNION operator and a group by can be helpful, especially when I just want to do a quick check against 2 tables to see if the data is in synch, like a production and development table. It's very quick to write:
select * from MyTable_prod
union
select * from MyTable_dev
If the data is exactly the same, the number of records returned should be the same for the separate queries and the UNIONed query.
July 26, 2005 at 7:19 am
SELECT DISTINCT name
FROM table1
WHERE NOT EXISTS
(SELECT name FROM table2 WHERE table1.name = table2.name)
July 26, 2005 at 7:58 am
A variation of Sam's:
SELECT TOP 100 PERCENT [rowid]
FROM [tableA]
WHERE [rowid] NOT IN (
SELECT TOP 100 PERCENT [rowid]
FROM [tableB]
)
ORDER BY [rowid]
– Albert Einstein
July 26, 2005 at 7:58 pm
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply