CODEWITHSUNDEEP
phpmysqlsql
Keaire
Keaire

Reputation: 899

Or condition in INNER JOIN

I have a table structured as follows (points):

id1 | id2 | p1 | p2
1     3     5    7
3     1     2    4
1     2     1    7

And another table strucuted as follows (users):

id | name
1    User1
2    User2
3    User3

So now, I need a query that specifing an ID (for example 3), the query check that the ID appears in the column id1 and id2, and if it appears in one of the two columns, it gives me back the user name with id1 and id2 from the rows selected. So, for example if I specific the ID 3, the query give me back:

name1 | name2 | p1 | p2
User1   User3   5    7
User3   User1   2    4

I tried various solutions but no way to do it, I think that I need an OR condition in the INNER JOIN but I don't know if it's possible and if it's the solution.. I didn't find nothing here.

I mean something like:

INNER JOIN users ON (users.id = points.id1) || (users.id = points.id2)

Any solution for that? Thanks

Upvotes: 0

Views: 174

Answers (2)

Ashutosh SIngh
Ashutosh SIngh

Reputation: 1019

Use case statement it will give you all matching value not need restricted for one or two values 

CREATE TABLE points (id1 int(2), id2 int(2), p1 int(2), p2 int(2));
INSERT INTO points VALUES(1,3,5,7);
INSERT INTO points VALUES(3,1,2,4);
INSERT INTO points VALUES(1,2,1,7);

CREATE TABLE users (id int(2), name char(23));
INSERT INTO users VALUES(1,'user1');
INSERT INTO users VALUES(2,'user2');
INSERT INTO users VALUES(3,'user3');    

    SELECT  (CASE WHEN u.id = p.id1 THEN u.name END) AS name1, 
    (CASE WHEN u1.id = p.id2 THEN u1.name END) AS name2, 
    p1, p2 
    FROM points p 
    INNER JOIN users u ON (u.id = p.id1) 
    INNER JOIN users u1 ON (u1.id = p.id2);

Upvotes: 0

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

Join the user table twice:

SELECT u1.name, u2.name, p.p1, p.p2 
  FROM points p
  JOIN users u1 ON u1.id = p.id1
  JOIN users u2 ON u2.id = p.id2
 WHERE u1.id = 3 OR u2.id = 3

Upvotes: 1

Related Questions