Mysql query returns 0

Question

I have a following query with subquery that returns no result

SELECT 'Message'
FROM message
WHERE ID in (
SELECT 'Message_id'
FROM user_message
WHERE 'status' =false )

Data in both tables is present. What could be the problem?

It should be select message from message where id in ( select message_id from user_message where status = false ). No single quotes but can have back ticks on column names. — Ravinder Reddy
– Ravinder Reddy, Commented Jun 14, 2014 at 6:22
@t.niese: I know that. But I don't think it is the OP's intention. OP unknowingly might have put that. See similar usage on other columns as well. — Ravinder Reddy
– Ravinder Reddy, Commented Jun 14, 2014 at 6:30

Ed Heal · Accepted Answer · 2014-06-14 06:23:45Z

2

Why are you using a sub-query?

Why not

SELECT Message
FROM message JOIN user_message ON ID = Message_id
WHERE status = false

Seems simpler

answered Jun 14, 2014 at 6:23

Ed Heal

60.3k18 gold badges91 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

back-ticks should replace single quotes.

@Ravinder - I know. missed some

Fabricator · Accepted Answer · 2014-06-14 06:21:44Z

1

You quoted the fields. Either do not quote the fields, or escape your fields with tick like this

SELECT `Message`
FROM message
WHERE ID in (
  SELECT `Message_id`
  FROM user_message
  WHERE `status` =false )

answered Jun 14, 2014 at 6:21

Fabricator

12.8k2 gold badges29 silver badges40 bronze badges

I prefer this solution. It will not generate duplicates if there are multiple matches in user_message.