CODEWITHSUNDEEP
mysql
camillavk
camillavk

Reputation: 521

SQL get user.emails from user_id list

I am trying to do a simple SQL query but am struggling to get it right.

I have a table called trips, that has a created_at column, as well as a user_id column (among other things).

I also have users table with names and emails.

I am trying to get a list of user names and emails that have posted a trip in the last 12 months.

I have written this query so far:

SELECT `user_id` 
FROM `trips`
WHERE trips.created_at > '2014-03-01 00:00:00'

Which gives me the user_id for the users that have created a trip in the last 12 months, but I don't know how to extend it so it gives me their name and emails from the user table.

Upvotes: 1

Views: 5126

Answers (4)

Rafay
Rafay

Reputation: 601

Use JOINS ..

SELECT t.user_id, u.user_id, u.user_name 
FROM trips t
JOIN users u on t.user_id=u.user_id
WHERE trips.created_at > '2014-03-01 00:00:00'

Upvotes: 0

yasser al-haj
yasser al-haj

Reputation: 1

select user_id, name, emails from trips where created_at>'2014-03-01 00:00:00'

Upvotes: -1

Arion
Arion

Reputation: 31239

You could also do it with a JOIN. Like this:

SELECT users.name ,users.email,users.`user_id` 
FROM `trips`
JOIN users
    ON user.user_id=trips.userid
WHERE trips.created_at > '2014-03-01 00:00:00'

Or you could do it with an exists:

SELECT
    users.name ,
    users.email,
    users.`user_id`
FROM
    users
WHERE EXISTS
(
    SELECT NULL 
    FROM trips
    WHERE trips.created_at > '2014-03-01 00:00:00'
    AND user.user_id=trips.userid
)

Upvotes: 0

Danyal Sandeelo
Danyal Sandeelo

Reputation: 12391

You need to query the other table as well. I assume your user table has name user. It has email name and email column

 SELECT user.name, user.email 
 FROM `trips`, user
 where user.id=trips.userid
 and trips.created_at > '2014-03-01 00:00:00'

You can use nested query as well, this query will show all the users based on the returned ids by the nested query.

 select * from user where id in (
  SELECT `user_id` 
  FROM `trips`
  WHERE trips.created_at > '2014-03-01 00:00:00'
 )

Upvotes: 3

Related Questions