Atif Azad Atif Azad - 6 months ago 17
MySQL Question

Subquery Syntax error for sql

Hi i am trying to fetch data from two tables(

products
and
issues
) in PHP and MySQL.

In
issues
table there is a column
status
which have four different values. The thing I want to do is to display
status
values in four different column for each product something like below:

S.No Product Name Status Val1 Status Val2 Status Val2 Status Val4


I am using below using below query

SELECT i.product_id, i.status, COUNT( * ) AS count FROM issues i
INNER JOIN products p ON i.product_id = p.id
GROUP BY `product_id`

Answer

Thank you guys, i have solved it

SELECT p.name,
(select count(*) from issues where status=val1 AND product_id=p.id) as Status Val1,
(select count(*) from issues where status=val2 AND product_id=p.id) as Status Val2,
(select count(*) from issues where status=val3 AND product_id=p.id) as Status Val3,
(select count(*) from issues where status=val4 AND product_id=p.id) as Status Val4
FROM issues i INNER JOIN products p 
ON i.product_id = p.id 
where p.branch_id = 1
GROUP BY product_id