Removing NULL value in SQL JOIN and UNION Operators in SQL

Question

[SOLVED] how to remove the NULL value only in the field provider_order only, in my case the table was using JOIN Table and UNION

Here is my database schema and My SQL Query

http://sqlfiddle.com/#!9/f77862/2

To clearly describe here i attach the picture

The red line circle is which should be removed

If you're satisfied that the problem is solved, please mark it as such by accepting an answer or otherwise delete the question, brotha. — Strawberry, May 23 '18 at 06:41

score 1 · Answer 1 · answered May 23 '18 at 04:26

1

What do you mean by remove? Removing entire row or just displaying something else other than NULL in provider_order. If you are looking for later, then you can use COALESCE

Edit : Made changes in your fiddle only : http://sqlfiddle.com/#!9/f77862/46

answered May 23 '18 at 04:26

Udit Solanki

531
5
12

score 0 · Accepted Answer · answered May 23 '18 at 04:29

You can not remove only that field in a row. However, you can get rid of the entire row simply by using a WHERE clause to filter out records with null values.

http://sqlfiddle.com/#!9/f77862/42

SELECT
    m.provider_id,
    m.provider_name,
    p.purchase_order_code,
    NULL AS purchase_order_sample_code,
    p.provider_id AS provider_order
FROM mst_provider AS m
LEFT JOIN trx_purchase_order AS p
    ON (m.provider_id = p.provider_id)
WHERE p.provider_id != NULL
UNION
SELECT
    m.provider_id,
    m.provider_name,
    p.purchase_order_code,
    NULL AS purchase_order_sample_code,
    p.provider_id AS provider_order
FROM mst_provider AS m
RIGHT JOIN trx_purchase_order AS p
    ON (m.provider_id = p.provider_id)
UNION
SELECT
    m.provider_id,
    m.provider_name,
    NULL,
    s.purchase_order_sample_code,
    s.provider_id
FROM mst_provider AS m
RIGHT JOIN trx_purchase_order_sample AS s
    ON (s.provider_id = m.provider_id)

this is what i expect, MANY THANKS brother – Bodronoyo Pro May 23 '18 at 04:32 — Bodronoyo Pro, May 23 '18 at 04:32

score 0 · Answer 3 · answered May 23 '18 at 04:34

Im assuming you want to filter where provider_order is null?

I wrapped your entire query as a subquery, and placed a where clause at the end, returning only records where provider_order is not null.

http://sqlfiddle.com/#!9/f77862/50/0

SELECT *
FROM (
    SELECT m.provider_id,
        m.provider_name,
        p.purchase_order_code,
        NULL AS purchase_order_sample_code,
        p.provider_id AS provider_order
    FROM mst_provider AS m
    LEFT JOIN trx_purchase_order AS p
        ON (m.provider_id = p.provider_id)

    UNION

    SELECT m.provider_id,
        m.provider_name,
        p.purchase_order_code,
        NULL AS purchase_order_sample_code,
        p.provider_id AS provider_order
    FROM mst_provider AS m
    RIGHT JOIN trx_purchase_order AS p
        ON (m.provider_id = p.provider_id)

    UNION

    SELECT m.provider_id,
        m.provider_name,
        NULL,
        s.purchase_order_sample_code,
        s.provider_id
    FROM mst_provider AS m
    RIGHT JOIN trx_purchase_order_sample AS s
        ON (s.provider_id = m.provider_id)
    ) _temp
WHERE provider_order IS NOT NULL

An another solution, thanks brotha... the problem already solved :-) — Bodronoyo Pro, May 23 '18 at 05:00
Lol I know I saw the other answer it was the same time I posted this one. This one only has one where clause, the other answer as three. I figured to give you a solution that closely resembles your code more, than injecting code in the middle of it. — sksallaj, May 23 '18 at 06:47

Removing NULL value in SQL JOIN and UNION Operators in SQL

3 Answers3