select where member only belongs to one customer

I am having trouble figuring out a query. I have three tables

messages
---------------
message_id
phone_num
body
received_time

subscribers
---------------
phone_num
keyword_id

keywords
---------------
keyword_id
client_id

Subscribers can belong to many keywords of different clients. I want to find recent messages of subscribers that belong to one particular client but no others, so only one client total.

For example looking for the most recent messages from subscribers who only belong to client 1, with the data:

message_id      phone_num   body         received_time
1               111         hi           123456
2               222         test         123489
3               333         msg          213445

phone_num   keyword_id
111         1
111         2
222         3
333         4
333         5

keyword_id      client_id
1               1
2               1
3               1
4               1
5               4

I would want to get:

message_id   phone_num   body   received_time
2            222         test   123489
1            111         hi     123456

S开发者_运维技巧ince numbers 111 and 222 only belong to one client

Make sense? I can't figure it out. Thanks

I builded this complex query:

SELECT
  m.message_id, m.phone_num, m.body, m.received_time

FROM
  messages m

WHERE m.phone_num IN (
  SELECT 
    phone_num 
  FROM 
    subscribers s,
    keywords k
  WHERE 
    s.keyword_id = k.keyword_id AND
    k.client_id = 1 AND
    s.phone_num IN (
      SELECT 
        s.phone_num
      FROM 
        subscribers s, keywords k
      WHERE
        s.keyword_id = k.keyword_id
      GROUP BY s.phone_num
      HAVING COUNT(DISTINCT k.client_id) = 1
    )
)

The most inner subquery fetches "Unique" numbers, e.g. numbers which belong to only one client.

The middle-inner subquery fetches among these numbers only those, which belong to desired client (note the k.client_id = 1).

And finally the outer query fetches messages, which numbers could be found in that phone list.

Note about performance: middle subquery is okay if you put an index on client_id field. The most inner query is a bit problematic, since it examines all rows of keywords table. What about size/number_of_rows in your tables?

Hope it will help you.

In a database with identical mock data, this query worked for me. It'll probably not be fast, but it will get the job done.

SELECT m.* FROM messages m

WHERE m.phone_num IN (
    SELECT DISTINCT s.phone_num FROM subscribers s, keywords k

    WHERE s.keyword_id = k.keyword_id
      AND k.client_id = ???
      AND NOT EXISTS (
          SELECT ki.client_id FROM keywords ki, subscribers si

          WHERE ki.keyword_id = si.keyword_id
            AND si.phone_num = s.phone_num
            AND ki.client_id <> k.client_id
          )
)

Neither of your queries would work for me, they froze up the server. Not sure why, probably bad indexing. But I figured out a query that runs pretty fast

SELECT DISTINCT m.message_id AS unique_id, m.phone_num, m.body, m.system_time
FROM messages m
JOIN subscribers s ON m.phone_num = s.phone_num
JOIN keywords k ON s.keyword_id = k.keyword_id
WHERE m.phone_num NOT
   IN (
      SELECT s.phone_num
      FROM subscribers s, keywords k
      WHERE k.client_id != 'XXXX'
      AND k.keyword_id = s.keyword_id
  )
ORDER BY m.system_time DESC

Thanks for the help and if anyone can improve my query, please do!