Mohammed Sufiyan Mohammed Sufiyan - 11 months ago 124
MySQL Question

MySQL select query using 2 conditions

My following query shows results only if dcno is exists in dc_detail table
but what i actually need is even then if dcno is not exists in dc_detail table it should define dcno as

0
and select that results also
Thanks in advance

SELECT p.po_no as id, DATE_FORMAT(p.po_date, '%d-%m-%Y')as po_date, p.customer, p.cust_po as po_no,p.tot_ord_qty,
DATE_FORMAT(p.delivery_date, '%d-%m-%Y')as delivery_date,p.dc_status,p.inv_status,p.tot_dc_qty,p.tot_inv_qty,
GROUP_CONCAT(distinct d.dc_no SEPARATOR ', ') as dc FROM po_header p, dc_details d
where p.cust_po=d.cust_po
group by p.cust_po;

Answer Source

You should use left join to include headers that have no details. That will give you rows with nulls for the detail fields that don't exist. To get the value 0 instead of null you can use coalesce function. The relevant parts of the query are

SELECT COALESCE(d.dc_no, 0),...
FROM po_header p LEFT JOIN dc_detail d ON p.cust_po=d.cust_po

Coalesce function returns the first non null argument.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download