Using an OR clause in my INNER JOIN

Question

I have the following two tables,

CREATE TABLE logins (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     user_id_1 INT NOT NULL,
     user_id_2 INT DEFAULT 0,
     user_id_3 INT DEFAULT 0,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

CREATE TABLE user_data (
    user_id NOT NULL,
    day DATE NOT NULL,
    PRIMARY KEY (`user_id, `day`)
) ENGINE=MyISAM;

This schema could use a refactor, but I've inherited it and have to write a query now that does a JOIN with both logins and user_data. I need to select all the rows in user_data that have a > 0 value for one of the three user_id_? keys.

I'm not entirely sure how to compile this query, was thinking something along the lines of:

 SELECT logins.user_id_1, logins.user_id_2, logins.user_id_3,   user_data.day,
        FROM logins 
        INNER JOIN user_data 
        ON (logins.user_id_1 = user_data.user_id OR ??)

What's the best way to query for this where I will retrieve up to 3 rows, one for each user_id_?

@Drew me too because usually you should have a better db structure and this should not be necessary. — Lelio Faieta, Oct 08 '15 at 17:48
the more I read this question, the more I am convinced that the schema is poorly designed. Hint, `user_id_n` columns and the need for a Junction Table approach (shameless marketing [here](http://stackoverflow.com/a/32620163)) — Drew, Oct 08 '15 at 17:58

JosephStyons · Answer 1 · 2015-10-08T18:55:51.797

2

OR is allowed. Another option is to left join to the user_data table three times, and check to see if any of them came back. You might want to try both and see which performs better. My guess is they will be about the same, but I'm not deeply familiar with the MySQL plan generator.

SELECT
  l.user_id_1
 ,l.user_id_2
 ,l.user_id_3
 --consider: what if there is a match in more
 --than one table?  what do you want to happen?
 ,case when ud1.day is not null then ud1.day
       when ud2.day is not null then ud2.day
       when ud3.day is not null then ud3.day
       else null
  end as day
FROM
  logins l
  left JOIN user_data ud1 on ud1.user_id = l.user_id_1
  left join user_data ud2 on ud2.user_id = l.user_id_2
  left join user_data ud3 on ud3.user_id = l.user_id_3
where ud1.user_id is not null
   or ud2.user_id is not null
   or ud3.user_id is not null

edited Oct 08 '15 at 18:55

answered Oct 08 '15 at 17:52

JosephStyons

57,317
63
160
234

I have several questions: 1. `,user_data.day` is not valid, because all your joined tables have aliases but no one called `user_data`? 2. why do yuo join table 3 times? what is the reason? – Alex Oct 08 '15 at 17:57
Due to index use, joining multiple times MIGHT be faster; but yeah, user_data.day would need handled differently. It also highlights a problem with the result fields defined question's initial attempt; if logins had values in more than one user_id field, multiple results would be returned with all those user_id values but only one "day", and have no indication of which user it was related to. – Uueerdo Oct 08 '15 at 18:00
@Alex, thanks for pointing out the error. I should have used the alias, and handled the three tables when selecting DAY. I joined to the table three times, but each time on a different field. The point is to find which of the three (if any) have a match. – JosephStyons Oct 08 '15 at 18:26
you are welcome. but I really don't like your query because of those 3 joins. and now you've added weird `CASE WHEN` which could be simply replaced by `COALESCE(ud1.day, ud2.day, ud3.day)` – Alex Oct 08 '15 at 18:35
@Alex: CASE WHEN is a legitimate construct. COALESCE is shorter, that is certainly also a legitimate way to solve the problem. In this case I do prefer CASE because it explicitly calls attention to the decision that must be made: which of the three do you want to use first? As for the multiple joins, I think there is nothing wrong with that. There are many cases where joining to the same table >1 time can be helpful, especially when hierarchical relationships are involved. But to each his own... – JosephStyons Oct 08 '15 at 18:52

Lelio Faieta · Answer 2 · 2015-10-08T17:53:43.003

0

you can use or.

SELECT logins.user_id_1, logins.user_id_2, logins.user_id_3,   user_data.day,
        FROM logins 
        INNER JOIN user_data 
        ON logins.user_id_1 = user_data.user_id OR 
        logins.user_id_2 = user_data.user_id OR 
        logins.user_id_3 = user_data.user_id

This syntax will work to choose all the records that match one or the other conditions.

edited Oct 08 '15 at 17:53

answered Oct 08 '15 at 17:48

Lelio Faieta

6,457
7
40
74

I dont think you can put multple `ON` that way. – Juan Carlos Oropeza Oct 08 '15 at 17:50
See [this answer](http://stackoverflow.com/questions/7183364/join-on-multiple-columns) for another example. Actually this question should be marked as duplicate for that one. – Lelio Faieta Oct 08 '15 at 17:51
your second on would be for bringing in table 3 – Drew Oct 08 '15 at 17:52
Yes, you can put multiple `OR` but not multiple `ON` – Juan Carlos Oropeza Oct 08 '15 at 17:52

Alex · Answer 3 · 2015-10-08T18:00:06.543

0

You can set any combinations of different conditions when do ON absolutely no limits for OR AND and other boolean operations same as for WHERE clause

    SELECT logins.user_id_1, logins.user_id_2, logins.user_id_3,   user_data.day,
    FROM logins 
    INNER JOIN user_data 
    ON logins.user_id_1 = user_data.user_id OR 
      logins.user_id_2 = user_data.user_id OR 
      logins.user_id_3 = user_data.user_id

or probably you don't need those logins.user_id_1, logins.user_id_2, logins.user_id_3, but just user_data.user_id:

    SELECT user_data.user_id,   user_data.day,
    FROM logins 
    INNER JOIN user_data 
    ON logins.user_id_1 = user_data.user_id OR 
      logins.user_id_2 = user_data.user_id OR 
      logins.user_id_3 = user_data.user_id

edited Oct 08 '15 at 18:00

answered Oct 08 '15 at 17:52

Alex

16,739
1
28
51

I had a typo in cut and paste. Actually my answer is exactly as yours and I too don't understand the db structure he is using – Lelio Faieta Oct 08 '15 at 17:55
@LelioFaieta ok, if typos I have no questions then :-) – Alex Oct 08 '15 at 17:58
there is no way I would have a logins table like that ! – Drew Oct 08 '15 at 18:05
@Drew why are you telling me that? I didn't provide schema, I just answered the question based on what OP has.And he explained, that he is not DB design author either. – Alex Oct 08 '15 at 18:10

score 0 · Answer 4 · answered Oct 08 '15 at 17:56

0

You could try this:

SELECT logins.user_id_1, logins.user_id_2, logins.user_id_3, user_data.day,
FROM   logins,user_data
WHERE  user_data.user_id in (logins.user_id_1,logins.user_id_2,logins.user_id_3)

answered Oct 08 '15 at 17:56

Shenglin Chen

4,504
11
11

score 0 · Answer 5 · answered Oct 08 '15 at 18:58

You might utilize a UNION, this will return multiple rows per login-row, don't know if you need this:

SELECT l.user_id, user_data.*
FROM user_data as u 
INNER JOIN
 (
   select user_id_1 --, could add other columns...
   from logins
   union
   select user_id_2 --, could add other columns...
   from logins 
   where user_id_2 > 0
   union
   select user_id_3 --, could add other columns...
   from logins 
   where user_id_3 > 0
 ) as l
ON l.user_id = user_data.user_id

Using an OR clause in my INNER JOIN

5 Answers5