c# or sql Inner Join

Question

if the preview doesnt come out right you can check the text file i post

Hi guys I really need help basically im trying to do a inner join update from table2 to table1. How to do the sql statement for this.

Example

table1

ID     Fruit     Color      ETag       Value

1      apple     red       TAG_TEST    4

2      orange    orange    TAG_TEST    3

3      cat       white     TAG_TEST    2

1      apple     red       TAG_TEST    1

1      apple      red      TAG_TEST    2

table2

Value     Etag

0         BAD

1         Good

2         pretty

3         ok

4         beautiful

table2 has the etag description of the value.
so 0 means bad, 1 means good, 2 means pretty etc.......

Im trying to pull the actual description from table2 and update the etag column where table1.Color -'red'

So basically im searching for the color red and the value red.

Sam Hobbs · Answer

UPDATE Table1 SET ETag = Table2.Etag FROM Table1 INNER JOIN Table2 ON Table1.Value = Table2.Value WHERE (Table1.Color = 'red')

Sam Hobbs · Answer

You can of course create a new thread (question) if you still need help with the login problem but if I understandd what you are saying then I would look at the connectin string when I got the failure; there should be something indicating what the mdf file is. You probably know better than I what a mdf file is.

David Smith · Answer

Thanks this helps out alot I just got to find out which mdf im talking to now Im getting this login failed error I made a mistake and erase the mdf and tried to replace it now im getting a login error when i try to run the program

David Smith · Answer

I take that back its working in microsoft access but not in my c# sql qeuries for my tables Im getting a error saying incorrect syntax near 'RIGHT' so how can i get it to work in c# sql for the table designer command text

David Smith · Answer

Hi I posted a accdb for you to try it out I cant seem to get it to work try your logic on this

Sam Hobbs · Answer

UPDATE Table2 RIGHT JOIN Table1 ON Table2.Value = Table1.Value SET Table1.ETag = Table2.Etag WHERE (((Table1.Color)=red)); Table1 ID Fruit Color ETag Value 1 apple red beautiful 4 2 orange orange TAG_TEST 3 3 cat white TAG_TEST 2 4 apple red Good 1 5 apple red pretty 2

David Smith · Answer

for table 1 there was a mistake the id column suppose to be 1 2 3 4 5 for table2 there is no ID coulumn there is just a value of a string representation and Etag column of a string representation there is no table 3 the result you have for table 3 is is suppose to be for table 1. table2 is a defintion table basically. now basically you search table1 Color field for red and look at the value for the row of red then go to the defnition table 2 and find out what the value you mean example if you look at ID 1 which is the first row there is a red and the value is 4. so now you go to table2 to find out what 4 mean. In this case 4 means beautiful. which comes from table 2 so you take the beautiful and update or replace in table 1 under the etag column you only search where there is a red. do it for the whole table. so the end result of table 1 should be table1 Table1 ID Fruit Color Etag Value 1 apple red beautiful 4 2 orange orange TAG_TEST 3 3 cat white TAG_TEST 2 4 apple red Good 1 5 apple red pretty 2 row 2 and 3 will not be updated because there is no red.

Sam Hobbs · Answer

You say  searching for the color red and the value red ; I am sorry I did not see that. To search for the value red add a where clause. I don't understand what you mean by  value red . Note also that the SQL I posted creates a third table if that is not what you need to do then we can adjust as needed.

Sam Hobbs · Answer

The input data I used is: Table1 ID Fruit Color ETag Value 1 apple red TAG_TEST 1 3 cat white TAG_TEST 2 1 apple red TAG_TEST 2 2 orange orange TAG_TEST 3 1 apple red TAG_TEST 4 Table2 Value Etag 0 BAD 1 Good 2 pretty 3 ok 4 beautiful and using the SQL I posted above the results I get is: Table3 ID Fruit Color Etag Value 1 apple red beautiful 4 2 orange orange ok 3 3 cat white pretty 2 1 apple red Good 1 1 apple red pretty 2 So did I use the wrong input data? Is the output I show above not correct?

Lee Sheene · Answer

SELECT Table1.ID Table1.Fruit Table1.Color Table2.Etag Table1.Value FROM Table2 LEFT OUTER JOIN Table1 ON Table2.Value = Table1.Value WHERE Table1.Color = 'red' ;

David Smith · Answer

The code is not replacing the Etags from table2 into table 1 do you know why I follow this example SELECT Table1.ID Table1.Fruit Table1.Color Table2.Etag Table1.Value INTO Table3 FROM Table2 RIGHT JOIN Table1 ON Table2.Value = Table1.Value;

David Smith · Answer

its not working

Sam Hobbs · Answer

SELECT Table1.ID Table1.Fruit Table1.Color Table2.Etag Table1.Value INTO Table3 FROM Table2 RIGHT JOIN Table1 ON Table2.Value = Table1.Value;

Amit Choudhary · Answer

hi friend first do one fetch the value for color red from table 1 using following query: select Value from table1 where color='red' Now you have to update the table 2 on the basis of values coming from the first query: so the final sql statement will be look like this: update table2 set Etag='pretty' where Value in (select Value from table1 where color='red') Hope it solve your problem. Please mark as answer if it helps you.

David Smith · Answer

the result table should be like this which is table1 ID Fruit Color ETag Value 1 apple red beautiful 4 2 orange orange TAG_TEST 3 3 cat white TAG_TEST 2 1 apple red Good 1 1 apple red pretty 2