mysql selecting data from one table based on data from another table

Question

I have two mysql tables

table1:

ID    data    status
1     data1   1
2     data2   1
3     data3   0
4     data4   1

table2:

ID    lastLoginDate    specialID
1     2018-09-10       abc
2     2018-03-16       xyz
3     2018-02-20       foo
4     2018-06-18       bar

The common data between both tables is the ID column.

I have a list of specific values I'd like to find in table2 but I would also like to include results that only include data from Table1 where the status is 1 AND where the lastLoginDate from Table2 is less than '2018-03-17'

Here's the code I've tried to use so far, but it does not work:

SELECT Table1.data, Table2.ID, Table2.specialID 
FROM Table1, Table2 
WHERE Table2.SpecialID IN ('acb, foo') OR Table2.ID IN 
  (SELECT Table2.ID 
  FROM Table1, Table2 
  WHERE Table1.ID = Table2.ID 
    AND Table1.status = '1' 
    AND Table2.lastLoginDate < '2018-03-17'
  )";

Expected Result:

data    ID    specialID
data1   1     abc
data2   2     xyz

The results shouldn't include ID '3' because the status is '0' in Table1 even though the specialID was included in the 'IN' list.

Also, the results shouldn't include ID '4' because the specialID was not include in the 'IN' list and the 'lastLoginDate' is greater than '2018-03-17'.

Again, there are 2 overall conditions 1. needs to be in the 'IN' list (only if the status is '1') 2. need to fall within the lastLoginDate (if shows up on the 'IN' list that should override this condition, however only if status is '1')

I'm not sure if this needs to be separated into two queries to work properly or not. Hoping someone can help me figure this out.

Try this, select t1.data, t1.ID, t2.specialID from table1 t1, table2 t2 where t1.ID = t2.ID and t1.status = 1 and t2.lastLoginDate < '2018-03-17' and t2.speciaID IN ('acb', 'foo') — Santosh, Sep 10 '18 at 15:08

score 2 · Accepted Answer · answered Sep 10 '18 at 15:13

2

If you wan't it to do either in the in list or less then a specific last log in you'll want something like this

select t1.data, t2.id, t2.specialID
from table1 t1
inner join table2 t2
    on t1.id = t2.id 
where t1.status = 1
    and (t2.specialID in('abc','foo') or t2.lastloginDate <'2018-03-17')

answered Sep 10 '18 at 15:13

saihtam8

36
3

1

Can you explain to the OP a bit more in detail what is inside your solution? – Lelio Faieta Sep 10 '18 at 15:19
Sure. What i'm doing is joining the two tables together with an inner join which implies that both sets of id's need to exist in each table. Then I'm checking that status id of 1 is set and finally the tricky bit is the and statement which checks to see if the special id exists or the last log in date is less then 2018-03-17. That and statement will return any records that meet either criteria inside of the () which is a good way to combined conditionals where you want an if this or that sort of result. – saihtam8 Sep 10 '18 at 15:29

score -1 · Answer 2 · answered Sep 10 '18 at 15:15

-1

I think you have overcomplicated things, a simple match by id should remove any results where there isn't a match by id (the same as doing an inner join), for the rest of the condition you can just add them to the where clause:

Select t1.data, t1.id, t2.specialID
from Table1 as T1, Table2 as T2
where t1.id = t2.id
and t1.status = 1
and t2.specialId in ('abc','foo')
and t2.login_date < '2018-03-17' ;

answered Sep 10 '18 at 15:15

Juan

5,525
2
15
26

implicit joins have been long deprecated and replaced by explicit joins that also increase readibility – Lelio Faieta Sep 10 '18 at 15:18
1

@LelioFaieta Implicit joins are treated just the same as explicit joins by the Database. What you say about readablitiy is argueble and in the end a matter of taste. Thanks for the downvote, even if it is a valid answer. – Juan Sep 10 '18 at 15:21
Even if it is a working answer it is not the correct one. Only with explicit join you have the full control on how to join the tables. – Lelio Faieta Sep 10 '18 at 15:26
@LelioFaieta Yes, but that full control (Outer, left, I assume) is not needed for this query. You need to distinguish between things that are wrong and things are not just as you would like them to be because they look better (to you). – Juan Sep 10 '18 at 15:45
have a look at questions like [this](https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) one to understand what i mean. – Lelio Faieta Sep 10 '18 at 15:48
@LelioFaieta https://stackoverflow.com/questions/5273942/mysql-inner-join-vs-where – Juan Sep 10 '18 at 15:50
so you agree with me! From the accepted answer you mentioned: _Performance wise, they are exactly the same (at least in SQL Server) but be aware that they are deprecating the implicit outer join syntax._ – Lelio Faieta Sep 10 '18 at 15:52
@LelioFaieta I don't think I have anything more to add. There is no outer join involved for this question and for the inner join there is no difference than using a where clause. With that said, have a nice day. – Juan Sep 10 '18 at 15:54

mysql selecting data from one table based on data from another table

2 Answers2