CODEWITHSUNDEEP
mysqlsqljoininner-join
Phil
Phil

Reputation: 14641

MySQL Inner Join failure

Table

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

Upvotes: 0

Views: 661

Answers (3)

Borealid
Borealid

Reputation: 98469

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'

Upvotes: 4

Diego
Diego

Reputation: 36136

SELECT ID,
CASE
    WHEN W1=W2 THEN W1
    ELSE W2
END CASE
FROM Table

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135799

SELECT t2.W2
    FROM YourTable t1
        INNER JOIN YourTable t2
            ON t1.W1 = t2.W1
    WHERE t1.W2 = 'q'

Upvotes: 1

Related Questions