Mysql join and grouping

[ddalton]

I want to join two tables

One is a relations table with multiple enteries for UserID and the other is a profile table with user information also has the UserID

I want to do a search on how often a relation occurs based on a search of the profile name and the same UserId in both.

Something like
select , count() AS cnt FROM profile, relations WHERE profile.name "
." LIKE '%$namesearch%' GROUP BY relations.UserID ORDER BY cnt DESC

but this does not work

[LordShryku]

You never speciy how to join the table, and your *'s are ambigious.
Try something like this instead

SELECT count(r.user_id) AS cnt 
  FROM profile p, relations r 
 WHERE p.user_id = r.user_id
   AND p.name LIKE '%$namesearch%'

[ddalton]

If I add to:

SELECT count(r.user_id) AS cnt
FROM profile p, relations r
WHERE p.user_id = r.user_id
AND p.name LIKE '%$namesearch%'
GROUP BY r.name, r.user_id
ORDER BY cnt

I do not get distinct r.name r.user_id pairs with a count for those pair matches.