CODEWITHSUNDEEP
phpmysql
Lawrence Cooke
Lawrence Cooke

Reputation: 1597

getting data from another table in mysql

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.

Upvotes: 0

Views: 39

Answers (1)

ASR
ASR

Reputation: 1811

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.

Upvotes: 1

Related Questions