CODEWITHSUNDEEP
mysql
Atif Azad
Atif Azad

Reputation: 527

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`

Upvotes: 0

Views: 72

Answers (3)

Atif Azad
Atif Azad

Reputation: 527

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

Upvotes: 0

Yogesh Singasane
Yogesh Singasane

Reputation: 273

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

Upvotes: 1

Buksy
Buksy

Reputation: 12238

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

Upvotes: 1

Related Questions