Ryan Ryan - 6 months ago 7
PHP Question

I need to return results from a 3 table join when one table has no records

I have been working on this for days and have not found a fix or solution yet. I am trying to return all results even when one table(table3) has no matching data. The default that I need to return is table1.ord_no, table2.item_no, table1.cus_no, table2.unit_price, table2.item_desc_1. If there is not a match in table3 that will give me a match for the .prc_or_disc_1 field I just need to echo 0. But as my query sits right now it will return nothing whenever table3 is empty I am assuming because of my AND table2.cus_no = table3.cd_tp_1_cust_no statement. But it would be great to get the results from table1 and table2. I have tried to use a left join, left outer join, and an outer join. Is there any fix?

SELECT table1.ord_no, table2.item_no, table2.item_desc_1,
table1.cus_no, table2.unit_price, table3.prc_or_disc_1, table2.line_seq_no
FROM table2 JOIN table1
ON table1.ord_no = table2.ord_no
LEFT OUTER JOIN table3 on table2.item_no = table3.cd_tp_1_item_no
Where table2.ord_no = $multi_orders
AND table2.cus_no = table3.cd_tp_1_cust_no
AND getdate() BETWEEN start_dt AND end_dt
ORDER BY table2.line_seq_no


I also tried this ridiculous query, which almost worked, but gave me an error that said I cannot receive more than one result.

IF (SELECT table3.prc_or_disc_1
FROM table3 join table1 on table1.cus_no = table3.cd_tp_1_cust_no
JOIN table2
ON table1.ord_no = table2.ord_no WHERE table1.ord_no = $multi_orders
AND table3.cd_tp_1_item_no = table2.item_no
AND table3.cd_tp_1_cust_no = table1.cus_no
AND getdate() between start_dt and end_dt) > 0
BEGIN
(SELECT table1.ord_no
, table2.item_no
, table2.item_desc_1
,table1.cus_no
, table2.unit_price
, table3.prc_or_disc_1
FROM table1
JOIN table2
ON table1.ord_no = table2.ord_no
JOIN cicmpy ON table1.cus_no = cicmpy.debcode
LEFT JOIN table3 on table1.cus_no = table3.cd_tp_1_cust_no
WHERE table1.ord_no = $multi_orders
AND table3.cd_tp_1_item_no = table2.item_no
AND table3.cd_tp_1_cust_no = table1.cus_no
AND getdate() between start_dt and end_dt)
END
ELSE
BEGIN
SELECT table1.ord_no
, table2.item_no
, table2.item_desc_1
,table1.cus_no
, table2.unit_price
FROM table1
JOIN table2
ON table1.ord_no = table2.ord_no
JOIN cicmpy ON table1.cus_no = cicmpy.debcode
WHERE table1.ord_no = $multi_orders;
END

Answer

You should put the condition table2.cus_no = table3.cd_tp_1_cust_no into the LEFT OUTER JOIN condition instead of the WHERE condition. Not only will that give you a result when there is no matching row in table3, but it also seems more logically to me, to do this more narrow join instead of filtering later.

So your query would then become:

SELECT table1.ord_no, table2.item_no, table2.item_desc_1,
        table1.cus_no, table2.unit_price, table3.prc_or_disc_1, table2.line_seq_no
    FROM table2 JOIN table1 ON table1.ord_no = table2.ord_no
        LEFT OUTER JOIN table3 ON table2.item_no = table3.cd_tp_1_item_no
            AND table2.cus_no = table3.cd_tp_1_cust_no
    WHERE table2.ord_no = $multi_orders
        AND getdate() BETWEEN start_dt AND end_dt
    ORDER BY table2.line_seq_no
Comments