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 |
|
jbpblog
Starting Member
4 Posts |
 Posted - 2010-01-11 : 00:22:20
|
| Table1 has four columns: ID(int),Name(nvarchar), RefCode(char),RefCodeValue(nvarchar)Table2 has two columns; RefCode(char), RefCodeValue(nvarchar)example: refCode | RefCodeValueAT | F.o.D.AT | Dc.o.D. CP | F.o.D.CP | ghhm I would like to return those records of table1 where the RefCodeValue and RefCode match in both tables. |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2010-01-11 : 00:24:54
|
| put a join,Select id,name,t1.RefCode,t2.RefCodeValue from table1 t1 inner jointable2 t1 on t1.RefCode=t2.RefCode and t1.RefCodeValue=t2.RefCodeValueSenthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-01-11 : 00:25:51
|
[code]select *from table1 inner join table2 on table1.refcode = table2.ref_code and table1.refcodevalue = table2.ref_codevalue[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
jbpblog
Starting Member
4 Posts |
Posted - 2010-01-11 : 00:32:08
|
| Thank you - and how would I reverse the query to get only the records from t1 that DON'T have a match on t2? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-01-11 : 00:34:18
|
[code]select *from table1 LEFT join table2 on table1.refcode = table2.ref_code and table1.refcodevalue = table2.ref_codevaluewhere t2.refcode is null[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-01-11 : 00:34:36
|
or use NOT EXISTS()
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
jbpblog
Starting Member
4 Posts |
Posted - 2010-01-17 : 10:48:55
|
| Thank you! |
|
|
|
|
|
|
|
|
match on two columns