CODEWITHSUNDEEP
sqlgoogle-bigquery
HG K
HG K

Reputation: 327

How to fill values by referencing other field values in BigQuery SQL?

I have these 2 tables

parents

name address
a one
b two
c three

children

name parents
1 a
2 a
3 b
4 a
5 c
6 b

I'd like to get the result like this,

children_address

name address
1 one
2 one
3 two
4 one
5 three
6 two

children's address is the same as their parents'.

How can I get this result in BigQuery?

Upvotes: 0

Views: 415

Answers (2)

Aleix CC
Aleix CC

Reputation: 2079

Assuming that all childrens have parents, I'd use INNER JOIN to achieve the desired output:

SELECT
  c.name, p.address
FROM
  parents_table AS p
INNER JOIN
  children_table AS c ON p.name = c.parents
ORDER BY 
  c.name

OUTPUT:

name address
---- -------
1    one
2    one
3    two
4    one
5    three
6    two

If you use LEFT JOIN, you are probably assuming that not all children have a parent. In case that happens, the result with return null on the address column of the output.

Upvotes: 1

Timogavk
Timogavk

Reputation: 869

Try this using LEFT JOIN

SELECT children.name, address FROM dataset.children children
LEFT JOIN dataset.parents parents ON parents.name = children.parents

Upvotes: 1

Related Questions