CODEWITHSUNDEEP
mysqlsqlleft-join
cwiggo
cwiggo

Reputation: 2609

Why isn't this mysql query working?

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?

Upvotes: 1

Views: 84

Answers (3)

ThW
ThW

Reputation: 19502

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.

Upvotes: 2

piet.t
piet.t

Reputation: 11921

When using FROM x_commmunities, x_community_invites you are doing a cross-join that combines every row of x_communities with every row from x_community_invites without doing any matching at all.

You have add some separate join-constraint to tell the DBMS how to find matching pairs:

WHERE x_communities.id = x_community_invites.community_id

This way you will get an inner join.

You could also use join-syntax in your from-clause:

FROM x_communities join x_community_invites on(x_communities.id = x_community_invites.community_id)

or if you want a outer join:

FROM x_communities left join x_community_invites on(x_communities.id = x_community_invites.community_id)

Upvotes: 1

valex
valex

Reputation: 24144

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'

Upvotes: 2

Related Questions