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
– Santosh, Commented Sep 10, 2018 at 15:08

saihtam8 · Accepted Answer · 2018-09-10 15:13:04Z

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, 2018 at 15:13

saihtam8

363 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2399035 Over a year ago

Can you explain to the OP a bit more in detail what is inside your solution?

saihtam8 Over a year ago

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.

Juan · Accepted Answer · 2018-09-10 15:15:07Z

-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, 2018 at 15:15

Juan

5,6112 gold badges17 silver badges27 bronze badges

8 Comments

user2399035 Over a year ago

implicit joins have been long deprecated and replaced by explicit joins that also increase readibility

Juan Over a year ago

@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.

user2399035 Over a year ago

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.

Juan Over a year ago

@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).

user2399035 Over a year ago

have a look at questions like this one to understand what i mean.

|

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related