从mysql中的另一个表获取数据

Question

I have a table with an item_id and some other fields.

item_id,name,address,postcode

I also have a table of messages that relate to an item_id

item_id,user_id,message

What I am trying to do is make a query that selects all rows in a specific postcode (not an issue there)

but also return something (0 or 1 for example) if the user that is logged in also has messages associated with the item

so I have a basic query like this:

select item_id,name,address,postcode from items where postcode in ('3000')

but I want to incorporate the messages table somehow so that I am left with a flag to show that the user_id has or has not sent a message related to the item

How can I do this?

I tried a left join, but that will only show results where the user has messages, whereas I want the result regardless of if the user actually sent a message, I just want to know if they did or not.

Answer 1

Use an IF condition to check the message field IF(message.message <>'', 1, 0) as flag

SELECT 
    items.item_id,name,address,postcode, 
    IF(message.message <>'', 1, 0) as flag, message 
FROM items 
LEFT JOIN message ON items.item_id = message.item_id 
WHERE postcode in ('3000')

Hope this will help.