Question: I have two tables:

**x\_community\_invites** :

```
id  community_id    from    to  registered  message seen    accepted    updated

```

and 

**x\_communities** :

```
id  name    tags    registered  updated

```

With the query:

```
$query = sprintf("SELECT x_commmunities.* FROM x_commmunities, x_community_invites WHERE x_community_invites.to = '%s' AND x_community_invites.accepted = '%d'", $id, 1);

```

My problem is that the query I run returns all the fields from the x\_communities table.

> 
> Example scenario:
> 
> 
> There are 2 communities in the x\_communities table:
> 
> 
> * id's - 1 and 2 name
> * 1stCommunity and 2ndCommunity
> 
> 
> There are 3 community invites in the x\_community\_invites table:
> 
> 
> * All different id's
> * 2 with the same community id as 1st Community, both to fields accepted
> * 1 with the same community id as 2nd Community, to = profile id and accepted = 1
> 
> 
> But with the query, it grabs all of the communities ids and names, for
>  some reason unknown to me.
> 
> 
> 

I want to return the community id and name where the x\_communities\_invites.to field is the user id and the x\_communities\_invites.accepted field is 1.

Also, what sort of query is the above query? Some sort of join, I can't find a similar query online with similar syntax.

Can you help me out here?

What am I doing wrong here?

Answer: 
You haven't linked tables. You should use JOIN:

```
SELECT x_commmunities.* 
FROM x_commmunities
JOIN x_community_invites  on x_commmunities.id=x_community_invites.community_id
WHERE x_community_invites.to = '%s' AND x_community_invites.accepted = '%d'

```
It is an implicit inner join, but the condition that connects the two tables is missing.

```
SELECT x_commmunities.id, x_commmunities.name, COUNT(x_community_invites.*) AS invites
  FROM x_commmunities, x_community_invites 
 WHERE x_commmunities.id = x_community_invites.community_id
   AND x_community_invites.to = 'some_id_value' 
   AND x_community_invites.accepted = '1'
 GROUP BY x_commmunities.id, x_commmunities.name

```

This could result in duplicates (multiple invites for the same community). GROUP BY aggregates the records by the provided fields.