Mysql select query data joining

Question

Here is my first query

SELECT 
    tax_productid,
    CONCAT(GROUP_CONCAT(tax_code SEPARATOR " "), " ", sum(tax_iva), "%") AS tax
FROM tb_tax t group by tax_productid order by tax_productid, prd_type desc;

which returns a proper tax applied on product (multiple tax type may applied on single product)

output data like.

tax_productid       tax
=========================
1                   tax1 tax2 10%
2                   tax1 tax2 20%
3                   tax1 tax2 30%

now there is a another table product and query is like

SELECT * FROM product ORDER BY productName;

return 10 product records.

I want to get product wise tax list but if there is no tax show null

SELECT 
    t1.*, 
    CONCAT(GROUP_CONCAT(tax_code SEPARATOR " "), " ", sum(tax_iva), "%") AS tax 
FROM 
    product t1, tb_tax t2 
WHERE
    t2.tax_product = t1.id;

this query returns only 3 rows. I want to list all products if table tb_tax has no entry for product then return null. so how can I acheive this.

xdazz · Accepted Answer · 2014-03-19 07:29:20Z

1

Your query is using INNER JOIN, you should use LEFT JOIN for this case:

SELECT 
    t1.*, 
    t2.tax    
FROM 
    product t1
LEFT JOIN (
    SELECT 
      tax_productid,
      CONCAT(GROUP_CONCAT(tax_code SEPARATOR " "), " ", sum(tax_iva), "%") AS tax
    FROM tb_tax 
    GROUP BY tax_productid 
) t2 ON t2.tax_productid = t1.id

edited Mar 19, 2014 at 7:29

answered Mar 19, 2014 at 7:09

xdazz

161k38 gold badges255 silver badges278 bronze badges

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

1 Comment

Mayank Pandya Over a year ago

Not working :( As you can see in first query I am using group_concat() and sum() which may requires group by clause. this query give a single row with concating all tb_tax data in one row.

Harsh Gupta · Accepted Answer · 2014-03-19 07:09:44Z

0

Use LEFT JOIN.

SELECT 
    t1.*,
    CONCAT(GROUP_CONCAT(tax_code SEPARATOR " "), " ", sum(tax_iva), "%") AS tax 
FROM 
    product t1
LEFT JOIN 
    tb_tax t2
ON 
    t2.tax_product = t1.id;

answered Mar 19, 2014 at 7:09

Harsh Gupta

4,5582 gold badges28 silver badges31 bronze badges

Collectives™ on Stack Overflow

Mysql select query data joining

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related