CODEWITHSUNDEEP
google-bigquery
AlwaysLearning
AlwaysLearning

Reputation: 41

BigQuery Two Tables (Join?) Name Lookup

I currently have two tables on Bigquery where I'm trying to look up the 'english name' of a specific address. Example tables below.

Table1

Address|Value
------------------
1111,55
2222,99
8888|33

Table2

Address|Name
------------------
1111|Bob
2222|Jill
8888|Steven

Desired Result after Query

Address|Name|Value
------------------
1111|Bob|55
2222|Jill|99
8888|Steven|33

Any help to do this in BigQuery would be appreciated.

Real Query

SELECT CASE WHEN tablename.from_number = '1231231234' THEN tablename.to_number ELSE tablename.from_number END AS party,
       COUNT(*) AS call_count,
       tablename.caller_address AS caller_address
FROM `bigquery-database` tablename
WHERE '1231231234' in (tablename.from_number, tablename.to_number)
GROUP BY party, caller_address
ORDER by call_count DESC

In summary, I'm trying to look up caller_address' first name which resides in another table under tablename2.firstname and display it in the query output. I can't figure out how to use (join?).

Pseudo Code

SELECT CASE WHEN tablename.from_number = '1231231234' THEN tablename.to_number ELSE tablename.from_number END AS party,
       COUNT(*) AS call_count,
       tablename.caller_address AS caller_address,

       Find where tablename.caller_address equals db.tablename2.caller_address and display first name from db.tablename2.name

FROM `bigquery-database` tablename
WHERE '1231231234' in (tablename.from_number, tablename.to_number)
GROUP BY party, caller_address
ORDER by call_count DESC

Upvotes: 0

Views: 294

Answers (1)

Johann
Johann

Reputation: 369

I think a simple inner join is what you need. 

select t1.address, t2.name, t1.value
from table1 as t1
inner join table2 as t2 on t1.address=t2.address

BigQuery Documentation

Upvotes: 2

Related Questions