CODEWITHSUNDEEP
mysqljoin
user1260310
user1260310

Reputation: 2227

mysql syntax join on two fields

I would like to join a table to another table where either of two fields in the first table are keys to the second table.

The second table just has some additional info I'd like to pull in if I get a match on either of the fields in the first table.

Table network

id | inviter | invitee | status

Table userinfo

id | userid | name

Query if were not accessing 2nd table

"SELECT * from network where inviter= '22' OR invitee ='22'"

I think it is something like the following but cannot figure out syntax:

"SELECT n.*,u.* from `network`, n
left join `users`, u on
n.inviter = u.id OR n.invitee= u.id
WHERE n.inviter = '22' or n.invitee= '22'"

Many thanks for any suggestions.

Upvotes: 0

Views: 265

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270617

You have the syntax almost correct except that you don't want a comma between the table and its alias a or u (optionally use the AS keyword), AND you need a second join against userinfo because you need different data for the inviter and invitee:

SELECT
  /* Use column aliases to distinguish inviter/invitee details */
  n.id AS n_id,
  n.status,
  inviter.userid AS inviter_userid,
  inviter.name AS inviter_name,
  invitee.userid AS invitee_userid,
  invitee.name AS invitee_name
FROM
  network AS n
  /* JOIN against `userinfo` twice: once to get inviter, once for invitee */
  /* aliased as inviter/invitee */
  LEFT JOIN userinfo AS inviter ON n.inviter = inviter.userid
  LEFT JOIN userinfo AS invitee ON n.invitee = invitee.userid
WHERE 
  n.inviter = 22
  OR n.invitee = 22

Update:

To return only one or the other of these which matches, you can modify it with a CASE statement to figure out which of them is the same as the value in the WHERE clause:

SELECT
  /* Use column aliases to distinguish inviter/invitee details */
  n.id AS n_id,
  n.status,
  /* Return only one name, whichever matches the same value as in WHERE */
  CASE 
    WHEN inviter.userid = 22 THEN inviter.name
    WHEN invitee.userid = 22 THEN invitee.name
  END AS name
FROM
  network AS n
  /* JOIN against `userinfo` twice: once to get inviter, once for invitee */
  /* aliased as inviter/invitee */
  LEFT JOIN userinfo AS inviter ON n.inviter = inviter.userid
  LEFT JOIN userinfo AS invitee ON n.invitee = invitee.userid
WHERE 
  n.inviter = 22
  OR n.invitee = 22

Update 2:

Okay, thinking harder about it, if all you need is the name of the id you already know (22), there is no need for 2 joins. You still need to use the CASE in a subquery but it only has to return the id. 

SELECT
  n.*,
  u.*
FROM (
  SELECT
    id AS n_id,
    status,
    CASE 
      WHEN inviter = 22 THEN inviter 
      WHEN invitee = 22 THEN invitee 
    END AS i_id
  FROM network
  WHERE inviter = 22 OR invitee = 22
) n JOIN userinfo u ON n.i_id = userinfo.userid

Upvotes: 1

Robert H
Robert H

Reputation: 11730

Try this: 

SELECT a.inviter, a.invitee, a.status,b.userid, b.name FROM network a INNER JOIN userinfo b ON a.id = '22' AND b.id = '22';

To use multiple keys for a join, try the following: 

SELECT SELECT a.inviter, a.invitee, a.status,b.userid, b.name FROM network a INNER JOIN userinfo b ON a.invitee = '22' AND b.userid = '22' OR a.inviter = '22' AND b.userid ='22';

Upvotes: 0

Related Questions