Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
 Posted - 2009-11-26 : 06:32:40
|
| Dear All,I am 2 table with one column on each table.I need to compare which are data are there or not.I mean i need to select the data which are not exist in one and exist in another.pls help its urgent. |
|
|
Buzzard724
Yak Posting Veteran
66 Posts |
Posted - 2009-11-26 : 07:04:40
|
| Presuming that the two tables are linked you could use a Count to test how many related records there are BUT there must be more than one column in each table as you will need a primary key and foreign key to relate the tables e.g.Select t1.t1_id, t1.column1, count(t2.column1) as CountRelatedRecords from table1 t1 left outer join table2 t2 on t1.t1_id = t2.t1_idthe left outer join will ensure that you get a count of 0 were there is no related record in table2 |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-11-26 : 07:17:47
|
quote:
Originally posted by Buzzard724
Presuming that the two tables are linked you could use a Count to test how many related records there are BUT there must be more than one column in each table as you will need a primary key and foreign key to relate the tables e.g.Select t1.t1_id, t1.column1, count(t2.column1) as CountRelatedRecords from table1 t1 left outer join table2 t2 on t1.t1_id = t2.t1_idthe left outer join will ensure that you get a count of 0 were there is no related record in table2
You should group by the columns t1.t1_id, t1.column1MadhivananFailing to plan is Planning to fail |
|
|
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
Posted - 2009-11-26 : 07:25:24
|
| Hi,I am using this query to do the same..But i am getting the 1 incorrect record..not sure how exactly i can find thatAny help appreciated..select distinct s.invoice_numberfrom snp_Invoice_Number_gambit swhere not exists(select distinct v.c1 from vw_OB v where s.invoice_number = v.c1) |
|
|
|
Buzzard724
Yak Posting Veteran
66 Posts |
Posted - 2009-11-26 : 09:48:54
|
| Madhivanan - thank you for the spotting the forgotten group by.gangadhara - please can you give the expected output and the actual output from your query?is this what you need?Select t1.t1_id, t1.column1, count(t2.column1) as CountRelatedRecords from table1 t1 left outer join table2 t2 on t1.t1_id = t2.t1_idwhere count(t2.column1) > 0 --or = 0 if you want to find where there are no matchesgroup by t1.t1_id, t1.column1 |
|
|
|
|
|
|
Query help