2

here is my code i wish anybody can help me to solve my problem. my problem is in c.count and p.count lines. each of them does different works.

SELECT tablesite.name,
tablesite.family,
tablesite.phone_number,
job_list.job_name,
p.COUNT(action.service_provider_id) as positive,
n.COUNT(action.service_provider_id) as negative
FROM tablesite
INNER JOIN relation
on tablesite.id_user=relation.user_id
INNER JOIN job_list
on relation.job_id=job_list.job_id 

LEFT JOIN action p
ON tablesite.id_user=action.service_provider_id
AND action.vote !='' AND action.customer_comment =''

LEFT JOIN action n
ON tablesite.id_user=action.service_provider_id
AND action.vote !='' AND action.customer_comment !=''


GROUP BY name, family,job_name, phone_number

i can not use p.count or n.count how can solve this problem.

error: #1630 - FUNCTION p.COUNT does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

Drew
  • 24,851
  • 10
  • 43
  • 78
sammy
  • 717
  • 4
  • 13

1 Answers1

1

stab in the dark, but based on aliases going back to same table name action at least

SELECT tablesite.name,
tablesite.family,
tablesite.phone_number,
job_list.job_name,
COUNT(p.service_provider_id) as positive,
COUNT(n.service_provider_id) as negative
FROM tablesite
INNER JOIN relation
on tablesite.id_user=relation.user_id
INNER JOIN job_list
on relation.job_id=job_list.job_id 

LEFT JOIN action p
ON tablesite.id_user=p.service_provider_id
AND p.vote !='' AND p.customer_comment =''

LEFT JOIN action n
ON tablesite.id_user=n.service_provider_id
AND n.vote !='' AND n.customer_comment !=''


GROUP BY name, family,job_name, phone_number
sammy
  • 717
  • 4
  • 13
Drew
  • 24,851
  • 10
  • 43
  • 78
  • i think its true but why values of positive and negative is same?! – sammy Nov 16 '15 at 21:09
  • dang now I gotta do data and query fix too :) will gander – Drew Nov 16 '15 at 21:09
  • ok because the query is wishful thinking. A `left join` brings them all in. So the count is the same on a rowcount basis – Drew Nov 16 '15 at 21:10
  • you could involve a CASE WHEN block up top with positive and negative, only have one `left join` max – Drew Nov 16 '15 at 21:12
  • case when something 1 else 0 in a sum – Drew Nov 16 '15 at 21:13
  • I solved this question: `FUNCTION p.COUNT does not exist` – Drew Nov 16 '15 at 21:16
  • ok i choose your answer but please answer my new question. do you want me create new question? – sammy Nov 16 '15 at 21:17
  • sure. Because it has nothing to do with this question. Also do a sqlfiddle with a few rows if necessary over there. People love it when you do that – Drew Nov 16 '15 at 21:18
  • stab in the dark means تیری در تاریکی in Persian lol. ok i call you for next question – sammy Nov 16 '15 at 21:20
  • I don't do RTL languages very well :) Still trying to learn Urdu – Drew Nov 16 '15 at 21:23
  • drew look here: http://stackoverflow.com/questions/33744966/repetitive-values-when-using-two-left-join-mysql try to learn persian ;) – sammy Nov 16 '15 at 21:29