On 25 Oct, 12:00, panchettone <pan_che_ttone@non_morde.bau> wrote:
> Hi, I'm trying to run this query:
>
> --->SELECT COUNT(v.id) as C, v.title, v.desc, v.cat, u.username, t.team_name
> FROM videos AS v
> INNER JOIN users AS u ON v.user_id = u.user_id
> INNER JOIN team_members tm ON tm.user_id = u.user_id
> INNER JOIN teams AS t ON tm.team_id = t.team_id
>
> but I get an error
> [
> #1064 - You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near '.title, v.desc' at line 1
> ]
>
> If I omit COUNT (but I need it...), it perfectly works.
> What am I doing wrong? How could I rewrite my query? Thanks!

Try:

SELECT COUNT(v.id) cnt, v.title, v.desc, v.cat, u.username,
t.team_name
FROM videos AS v
INNER JOIN users AS u ON v.user_id = u.user_id
INNER JOIN team_members tm ON tm.user_id = u.user_id
INNER JOIN teams AS t ON tm.team_id = t.team_id

>
> SELECT COUNT(v.id) cnt, v.title, v.desc, v.cat, u.username,
> t.team_name
> FROM videos AS v
> INNER JOIN users AS u ON v.user_id = u.user_id
> INNER JOIN team_members tm ON tm.user_id = u.user_id
> INNER JOIN teams AS t ON tm.team_id = t.team_id
>




a new error:
[
#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP
columns is illegal if there is no GROUP BY clause
]

On 25 Oct, 12:27, panchettone <pan_che_ttone@non_morde.bau> wrote:
> > SELECT COUNT(v.id) cnt, v.title, v.desc, v.cat, u.username,
> > t.team_name
> > FROM videos AS v
> > INNER JOIN users AS u ON v.user_id = u.user_id
> > INNER JOIN team_members tm ON tm.user_id = u.user_id
> > INNER JOIN teams AS t ON tm.team_id = t.team_id
>
> a new error:
> [
> #1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP
> columns is illegal if there is no GROUP BY clause
> ]

Well that's basic GROUP BY syntax!

SELECT COUNT(v.id) cnt, v.title, v.desc, v.cat, u.username,
t.team_name
FROM videos AS v
INNER JOIN users AS u ON v.user_id = u.user_id
INNER JOIN team_members tm ON tm.user_id = u.user_id
INNER JOIN teams AS t ON tm.team_id = t.team_id
GROUP BY v.title, v.desc, v.cat, u.username, t.team_name

On 26 Oct, 05:19, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Captain Paralytic wrote:
> > On 25 Oct, 15:07, panchettone <pan_che_ttone@non_morde.bau> wrote:
> >>> OH, well you had better argue that out with MySQL because it says you
> >>> do!
> >>> If you are not happy with the syntax supplied by the MySQL product and
> >>> SQL, you had better write your own RDBMS and your own SQL standard!
> >> ok, I didn't want to start a fight, sorry for saying what I said, it was
> >> my ignorance speaking for me. I was just wondering -since my query is
> >> just working- if that group-by is something that can alter the way the
> >> results are sorted by (the entire query I did not post ends with an
> >> order by desc)
>
> > Jerry and I aren't fighting - yet!
>
> Aw, come on, Paul, put up your dukes! :-)
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

Yeah, you wanna come outside (of usenet) and say that }-()