CODEWITHSUNDEEP
mysqlsqlselect
aug
aug

Reputation: 11714

How to create a dummy value column in MySQL depending on the Table specific information was selected from?

I have two tables that contain information about users. One table, current_users keeps track of current users and the other table, new_users, contains data of users who have recently submitted their information. The information in both tables are identical in terms of attributes but the information is kept in separate for reasons.

As of right now I am selecting the information and LEFT JOIN-ing both new_users and current_users onto a table that has information of where these users have attended an event. Because other people work with these tables, I can't change the table by adding a column so I need a way to differentiate what information comes from what table in a SQL statement.

The SELECT statement as of right now selects from both tables simultaneously:

SELECT current_users.lastname, current_users.firstname, current_users.id, new_users,lastname, new_users.firstname, new_users.id

But I would like a way to create a dummy column that stores 1 whether current_users columns are NULL or stores 0 when new_users is NULL. Any advice?

Upvotes: 1

Views: 7862

Answers (2)

PinnyM
PinnyM

Reputation: 35531

SELECT current_users.lastname, 
       current_users.firstname, 
       current_users.id, 
       new_users,lastname, 
       new_users.firstname, 
       new_users.id,
       CASE WHEN new_users.id IS NULL THEN 0 ELSE 1 END as dummy_column

Upvotes: 3

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

create a view, maybe ?

create or replace view all_users
as 
 (select 0 as user_type, lastname, firstname, id from current_users
 UNION
 select 1 as user_type, lastname, firstname, id from new_users)

Upvotes: 1

Related Questions