MySQL SELECT COUNT（*）基于多个条件的两个表

Question

I have some sort of logic/sql challenge. I have two tables that hold user data and are related by a id. I would like to select the count of rows based on conditions from the two tables.

Here are my tables.

table one

t_contacts
id  | name    | tel     | profession | email   | sex  | city    | state   |
int | varchar | varchar | int        | varchar | char | varchar | varchar |

table two

t_contacts_meta
cid                | interest     |
int (t_contact_id) | varchar      |

from the table, contacts are stored on the t_contacts table and their interest stored on the t_contacts_meta table. One contact could have multiple interests.

My issue is the SQL query. example

 SELECT COUNT(*) as count 
 FROM t_contacts 
 where `state`='$state' 
    AND `profession`='$profession' 
    AND `sex`='$sex' 
    AND (
        `t_contacts_meta.interest`='$interest_1' 
        OR `t_contacts_meta.interest`='$interest_2 
        OR `t_contacts_meta.interest`='$interest_n'
    )

The sql i provided above is not my working code its just an example. I dont even know how to go about the query.

Thanks for the help :)

Answer 1

This is you're probably looking for:

SELECT COUNT(C.id) AS [count]
FROM t_contacts C
INNER JOIN t_contacts_meta M ON M.cid = C.id
                               AND M.interest IN ('interest_1', 'interest_2', 'interest_n')
WHERE C.state = 'state_value'
    AND C.profession = 'profession_value'
    AND C.sex = 'sex_value'

Hope this will help you.