select all members using mysql join

Question

I want to select all member who created something yesterday

TABLE users

userid   firstname    lastname
1         JOHN         DEO
2         JANE         DEO

TABLE msg

msg_id  msg_from   msg_to     received  age  city  country   timestamp
1       userid(1)  userid(2)  null      26   any   any       2018-10-04 14:37:12

Query

 SELECT
  SND.userid,
  SND.firstname,
  SND.lastname,
  M.age,
  M.city,
  M.country
FROM
  msg as M
  LEFT JOIN users as SND ON SND.userid = M.msg_from
WHERE
  M.timestamp >= (CURDATE() - INTERVAL 1 DAY);

I want to select all members who created something yesterday

Welcome ! Please explain your problem . What is the meaning of something in 1 day? — Suresh, Oct 06 '18 at 08:12
update your question add a proper data sample and the expected result — ScaisEdge, Oct 06 '18 at 08:13
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Oct 06 '18 at 08:13
i want to select members who created something like yesterday — mike joe, Oct 06 '18 at 08:20
Read: [Why is Select * considered harmful](https://stackoverflow.com/questions/3639861/why-is-select-considered-harmful) — Madhur Bhaiya, Oct 06 '18 at 08:48

Szekelygobe · Answer 1 · 2018-10-06T10:46:48.887

0

Try this:

SELECT SND.userid, 
       SND.firstname, 
       SND.lastname, 
       M.age, 
       M.city, 
       M.country
   FROM msg as M
     LEFT JOIN users as SND ON SND.userid = M.msg_from
   WHERE M.timestamp >= ( CURDATE() - INTERVAL 1 DAY );

You need to be consistent with the table aliases.

Ps.: I did not check the WHERE clause of you'r query.

edited Oct 06 '18 at 10:46

answered Oct 06 '18 at 08:44

Szekelygobe

2,309
1
19
25

thanks @Szekelygobe. thou it has still failed to work – mike joe Oct 06 '18 at 09:37
You have a syntax error at the end of the SQL query. Of course, the OP should also be able to find that error, but they have been persistent in not following error messages. – Corion Oct 06 '18 at 10:02
the syntax error should be not putting "before select to be "query" like that and remove ). there nothing else. and here i score like Query Score 65% – mike joe Oct 06 '18 at 10:17
@Corion you can see the edit i made which is clear but still not getting results from db. so that means this doesn;t solve the issue – mike joe Oct 06 '18 at 10:21

score -1 · Answer 2 · answered Oct 06 '18 at 10:35

-1

finally got it fixed. i had problem with the whole query and table

answered Oct 06 '18 at 10:35

mike joe

27
1
7

select all members using mysql join

2 Answers2