Subquery Syntax error for sql

Question

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`

What sub-query? 'product_id' Those look like the wrong quotes to me. — Jonnix
– Jonnix, Commented May 20, 2016 at 10:11

Buksy · Accepted Answer · 2016-05-20 10:23:46Z

1

If you just want to display all assigned statuses to a product, there is no need for subquery (in case there is a finite number of statuses)

If your table structure is similar to this:

CREATE TABLE products
    (`id` int, `name` varchar(4))
;
CREATE TABLE issues
    (`product_id` int, `status` varchar(2))
;

Then you can use this simple query

SELECT p.id, p.name, i1.status AS 'S1', i2.status AS 'S2', i3.status AS 'S3', i4.status AS 'S4'
FROM products p
LEFT JOIN issues i1 ON p.id=i1.product_id AND i1.status = "S1"
LEFT JOIN issues i2 ON p.id=i2.product_id AND i2.status = "S2"
LEFT JOIN issues i3 ON p.id=i3.product_id AND i3.status = "S3"
LEFT JOIN issues i4 ON p.id=i4.product_id AND i4.status = "S4"

Here is sqlFiddle

answered May 20, 2016 at 10:23

Buksy

12.4k9 gold badges65 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yogesh Singasane · Accepted Answer · 2016-05-20 10:29:39Z

1

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

GROUP BY product_id

answered May 20, 2016 at 10:29

Yogesh Singasane

2733 silver badges12 bronze badges

Comments

Fabian N. · Accepted Answer · 2016-05-27 20:12:32Z

0

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

edited May 27, 2016 at 20:12

Fabian N.

3,8682 gold badges27 silver badges47 bronze badges

answered May 20, 2016 at 10:19

Atif Azad

5271 gold badge4 silver badges15 bronze badges

Collectives™ on Stack Overflow

Subquery Syntax error for sql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related