CODEWITHSUNDEEP
mysqlsqlselect
Mayank Pandya
Mayank Pandya

Reputation: 1623

Mysql select query data joining

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.

Upvotes: 0

Views: 61

Answers (2)

xdazz
xdazz

Reputation: 160833

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

Upvotes: 1

Harsh Gupta
Harsh Gupta

Reputation: 4538

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;

Upvotes: 0

Related Questions