LucasP LucasP - 6 days ago 5
MySQL Question

Add column based on join condition

I want to create a column with a value based on the condition of a join. Best viewed in an example:

Table 1: Items

user_id
,
item_id
,
item_property


Table 2: Items user liked
user_id
,
item_id


Expected result:

Table 3: Items
user_id
,
item_id
,
item_property
,
new_column


Where the value of
new_column
is either
1
if the user_id, item_id pair (found in table 1) is present in the Table 2,
0
otherwise.

How can i create a MySql statement that achieves the following?

Answer

You can do something like this for a given dataset of this nature:

create table items (
  user_id int,
  item_id int,
  item_property text
);
create table liked (
  user_id int,
  item_id int
);

insert into items values
(1, 1, 'Ball'), (1, 2, 'Bat'), (1, 3, 'Glove'),
(2, 4, 'Jar'), (2, 5, 'Cooker'), (2, 6, 'Stove'),
(3, 7, 'Ram'), (3, 8, 'CPU'), (3, 9, 'SSD');

insert into liked values 
(1, 2), (2, 6), (3, 7);

Query

select i.*, case when l.user_id is null then 0 else 1 end as liked
from items i
left join liked l
  on i.user_id = l.user_id
  and i.item_id = l.item_id

Result

| user_id | item_id | item_property | liked |
|---------|---------|---------------|-------|
|       1 |       2 |           Bat |     1 |
|       2 |       6 |         Stove |     1 |
|       3 |       7 |           Ram |     1 |
|       1 |       1 |          Ball |     0 |
|       1 |       3 |         Glove |     0 |
|       2 |       4 |           Jar |     0 |
|       2 |       5 |        Cooker |     0 |
|       3 |       8 |           CPU |     0 |
|       3 |       9 |           SSD |     0 |

Recommendation

Don't create a new table with an additional column. Just run the appropriate SQL query to get the actual fields in the table of your liking and additional derived/calculated fields.

To simulate a table, you could create a view like this:

create view items_liked 
as
select i.*, case when l.user_id is null then 0 else 1 end as liked
from items i
left join liked l
  on i.user_id = l.user_id
  and i.item_id = l.item_id;

That way, you can always run select * from items_liked and retrieve the data you wanted without having to create a table.

An example is available here: http://sqlfiddle.com/#!9/8a4ef/1

Comments