How can I get match with any one of Table2
-
Dear All, I have a question about joining 2 table. e.g. table1 contains user---inv---amount---data2 a --- 001--- 1000 --- dfsdf st --- 001--- 1200 --- sdfs b --- 002--- 1300 --- sdf c --- 004--- 1400 --- sdf e.g. table2 contains cid--- data--- FromCountry 001--- acd--- USA 001--- efg --- USA 001--- okk --- USA 002--- cce --- CHI 003--- eer --- UK 003--- err --- UK 004--- edr --- VOC I would like to construct a table base on table1, and include the field "FromCountry" from table2. I use select table1.user, table1.inv, table2.FromCountry From table1, table2, Where table1.inv = table2.cid when joining 2 table, I found there are duplicated records generated... How can I just get the "FromCountry" from any one of "cid" record in table2? The perfer result on new table is: user --- inv --- FromCountry a --- 001 --- USA st --- 001 --- USA b --- 002 --- CHI c --- 004 --- VOC PLEASE HELP
-
Dear All, I have a question about joining 2 table. e.g. table1 contains user---inv---amount---data2 a --- 001--- 1000 --- dfsdf st --- 001--- 1200 --- sdfs b --- 002--- 1300 --- sdf c --- 004--- 1400 --- sdf e.g. table2 contains cid--- data--- FromCountry 001--- acd--- USA 001--- efg --- USA 001--- okk --- USA 002--- cce --- CHI 003--- eer --- UK 003--- err --- UK 004--- edr --- VOC I would like to construct a table base on table1, and include the field "FromCountry" from table2. I use select table1.user, table1.inv, table2.FromCountry From table1, table2, Where table1.inv = table2.cid when joining 2 table, I found there are duplicated records generated... How can I just get the "FromCountry" from any one of "cid" record in table2? The perfer result on new table is: user --- inv --- FromCountry a --- 001 --- USA st --- 001 --- USA b --- 002 --- CHI c --- 004 --- VOC PLEASE HELP
here it is:
SELECT DISTINCT table1.[USER],table1.inv,table2.fromcountry FROM table1,table2 WHERE table1.inv=table2.cid ORDER BY table1.inv
I Love T-SQL "Don't torture yourself,let the life to do it for you."
modified on Wednesday, August 13, 2008 8:37 AM