Below is an example table

Im trying to figure out the best way to populate the address when they are missing. I have my main table and i'll be able to join onto another table using the postcode.

Im just not sure what the best sytax would be as im dealing with "" and not nulls.

Main Table

Left join table

CodePudding user response：

Im just not sure what the best sytax would be as im dealing with "" and not nulls.

Consider below

select if(t1.Address = '', t2.Address, t1.Address) as Address, t1.PostTown, t1.Postcode
from Main_Table t1
left join Lookup_Table t2
using (Postcode)

CodePudding user response：

use Coalesce to replace wioth B.Address if A.Address is null

SELECT Coalesce(A.Address,B.Address) Address,A.PostTown,A.Postcode FROM `[project-name].[dataset-name].table_main` A
left join `[project-name].[dataset-name].table1` B on (A.Postcode=B.postcode)

CodePudding user response：

You can use the JOIN statement. In this case I get the fields from both tables. If one field is null this query shows the field in blank.

You can see this example:

SELECT B.Address,A.PostTown,A.Postcode FROM `[project-name].[dataset-name].table_main` A
left join `[project-name].[dataset-name].table1` B on (A.Postcode=B.postcode)

This query validates the fields B.Address and A.PostTown if they are nulls. If one field is null, you can put the text that you want.

You can see this example:

with main as (
SELECT B.Address,A.PostTown,A.Postcode FROM `[project-name].[dataset-name].table_main` A
left join `[project-name].[dataset-name].table1` B on (A.Postcode=B.postcode)
)
 
select IFNULL(main.Address,'information missing') as Address,IFNULL(main.PostTown,'information missing'),main.PostCode from main
order by Address desc