Using mysql in to display only values present in register using group_concat()

Question

I have two tables incoming_sms and subscribers_register that stores all of the numbers of customers.The table incoming_sms has lots of data from very many numbers but i would only want to select messages by my subscribers.

I have this query i am trying to use to show only messages from my subscribers but returns 0 results

SELECT dest_msisdn, text_message  FROM incoming_sms where dest_msisdn in (SELECT GROUP_CONCAT(msisdn) FROM subscribers_register);

Why am i getting 0 results?.

Answer 1

Have a look at the help for GROUP_CONCAT. It's going to return a long string of all the msisdn's instead of a list of all the msisdn's.

You could do something like this:

SELECT
    dest_msisdn,
    text_message 
FROM incoming_sms
WHERE dest_msisdn in
    (SELECT msisdn FROM subscribers_register)

... or you may find that an inner join is faster to execute:

SELECT
    dest_msisdn,
    text_message 
FROM incoming_sms
INNER JOIN subscribers_register
ON incoming_sms.dest_msisdn = subscribers_register.msisdn