ID W1 W2 1 a q 2 a w 3 b r 4 c t What I would like to obtain is, using a single SQL statement, to query the table using the W2, and get W2 in return where the W1 is the same.
i.e.:
query the table, ask for 'q'. q has 'a' for it's w1 so I want to bring the w2 of rows which also have 'a' in their w1.
How can I achieve this? It is inner join I believe but I am failing big time.
Thank you
For something simple like this, a nested query might be easier (and will probably be just as fast, or faster if you get accidental multiplicative results from a join):
SELECT * FROM TableName WHERE W1 IN (SELECT W1 FROM TableName WHERE W2='q') You could, indeed, also do this with an inner join.
Implicitly:
SELECT * FROM TableName t1, TableName t2 WHERE t1.W1=t2.W1 AND t1.W2='q' On explicitly:
SELECT * FROM TableName t1 INNER JOIN TableName t2 ON t1.W1=t2.W1 WHERE t1.W2='q' 
WHERE t1.w2='q' needs to be moved to the end of the statement.SELECT t2.W2 FROM YourTable t1 INNER JOIN YourTable t2 ON t1.W1 = t2.W1 WHERE t1.W2 = 'q' 
SELECT ID, CASE WHEN W1=W2 THEN W1 ELSE W2 END CASE FROM Table 