CODEWITHSUNDEEP
mysqlsql
Brad
Brad

Reputation: 12262

sql conditional when users id does not show up in another table

I have two tables, both contains a created_at column.

users: id, first_name, last_name, created_at

entries: id, user_id, created_at

Below is the query that returns all entries and the users, but I need to add a conditional to display the appropriate created_at date

select users.id, first_name, last_name, entries.created_at 
from users left join sweepstakes_entries on users.id = entries.user_id;

I imported a csv of mail-in entries into the users table and I need to write a sql statement that returns all entries with the user that entered them, as well as the mail entries I entered into the users table.

I used a left join to return the users that were imported from the csv file, since they don't have any entries in the entries table, but I still need to return them in the sql results.

With that said, I need to display the created_at date for when the entry was created from the entries table (entries.created_at), but I need to do a conditional on the created_at so when it pulls in the users (users who were imported from the mail-in csv) it will not have a created_at date from the entries table to tie it to, so I need to do a conditional that instead of using entries.created_at, I use users.created_at.

In theory:

if(this record's users.id does not show up in the entries table) use users.created_at as created_at else use entries.created_at as created_at

Upvotes: 0

Views: 46

Answers (2)

GGio
GGio

Reputation: 7653

You have multiple options:

  1. Use COALESCE - Returns first non null value from a list. SELECT COALESCE(Col1, Col2, Col3)

  2. Use CASE WHEN, THEN to check if Col1 is null or = "0000-00-00" return Col2 or vice versa

Upvotes: 2

GarethD
GarethD

Reputation: 69759

By the sounds of it you could simply use COALESCE

select users.id, 
    first_name, 
    last_name, 
    COALESCE(entries.created_at, users.created_at) AS created_at
from users 
    left join sweepstakes_entries on users.id = entries.user_id;

You could also use the less portable IFNULL:

IFNULL(entries.created_at, users.created_at) AS created_at

Upvotes: 1

Related Questions