CODEWITHSUNDEEP
mysqlnullviewmysql-workbench
Kingfox
Kingfox

Reputation: 129

MySQL views where a column is NULL

My headache has begun since Ive tried to make this MySQL view, where the outcome value for one of the columns is NULL. I need to have a real value like 0 instead.

I take my id from table1 and compare it in table2, so its not sure that there is a number with that id, if there is no number in table2 the value will turn up NULL and that where i need 0 instead. Here goes my code for the view:

CREATE VIEW `instock` AS
SELECT 
    table1.name AS name,
    table1.supl AS supply,
    table2.num AS numbers,
    table1.maxnum AS maxnumbers
FROM
    (table1
    LEFT JOIN table2 ON ((table1.id = table2.id)))
ORDER BY table1.name

Its the column numbers where i have a NULL value

Upvotes: 2

Views: 1638

Answers (2)

PerlDuck
PerlDuck

Reputation: 5730

I'm not sure about your left join in the query. A left join gives all rows from the left table (table1) including those for which there's no counterpart in the other table (table2). For these unmatched rows from the right table you will get NULL for all table2's columns, including table2.num.

Perhaps you are looking for an inner join. It depends on your data and whether your table2.num is NULLable. To just replace the NULLs with zeroes, use either COALESCE

CREATE VIEW `instock` AS
SELECT 
    table1.name AS name,
    table1.supl AS supply,
    COALESCE(table2.num,0) AS numbers,
    table1.maxnum AS maxnumbers
FROM
    (table1
    LEFT JOIN table2 ON ((table1.id = table2.id)))
ORDER BY table1.name

Or IFNULL

CREATE VIEW `instock` AS
SELECT 
    table1.name AS name,
    table1.supl AS supply,
    IFNULL(table2.num,0) AS numbers,
    table1.maxnum AS maxnumbers
FROM
    (table1
    LEFT JOIN table2 ON ((table1.id = table2.id)))
ORDER BY table1.name

If you want to completely skip items from table2 that are not in table1 (wrt. the ID field), you could use an inner join:

CREATE VIEW `instock` AS
SELECT 
    table1.name AS name,
    table1.supl AS supply,
    table2.num AS numbers,
    table1.maxnum AS maxnumbers
FROM
    (table1
    INNER JOIN table2 ON ((table1.id = table2.id)))
ORDER BY table1.name

Again: It depends on your needs. IFNULL/COALESCE will show 0 instead of NULL, INNER JOIN will skip the lines completely.

(When in doubt, I always refer to this explanation on joins. It's worth a mint.)

Upvotes: 1

Pastor
Pastor

Reputation: 318

You can use ifnull function:

SELECT 
  table1.name AS name,
  table1.supl AS supply,
  ifnull(table2.num,0) AS numbers,
  table1.maxnum AS maxnumbers
FROM
  (table1
  LEFT JOIN table2 ON ((table1.id = table2.id)))
ORDER BY table1.name

Upvotes: 1

Related Questions