大查询SELECT语句中的JOIN ERROR:使用LEFT JOIN的USING OR运算符 [英] JOIN ERROR in big query SELECT statement: USING OR Operator with LEFT JOIN

问题描述

我有以下选择语句.我正在使用LEFT JOIN将两个表推在一起.LEFT JOIN应该在两种情况下起作用:

I have the below select statement. I am using a LEFT JOIN to push two tables together. There are two conditions where the LEFT JOIN should work:

条件1:ATTOM_ID.ATTOM ID是唯一的标识符
条件2:ZIP，姓氏和完整地址.这些字段是字符串字段，所有三个字段都必须匹配才能成为JOIN.

Condition 1: ATTOM_ID. ATTOM ID is the unique Identifier
Condition 2: ZIP, LAST NAME, and FULL ADDRESS. These fields are string fields and all three must match to be a JOIN.

任何其他条件都将导致NULL，因此将LEFT JOIN.如果任何一个条件通过，都应该发生JOIN，这就是为什么我要在这里使用OR语句.

Any other condition should result in a NULL, hence the LEFT JOIN. If either condition passes, the JOIN should occur, which is why I wanted an OR statement here.

由于某种原因，Google Big Query不喜欢该查询，因为其中包含OR.我得到的错误是:

for some reason Google Big Query does not like the query because I have an OR in it. The error I get is:

LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.

这是SQL语句.此声明上的所有其他问题均起作用.使用LEFT JOINS和"OR"运算符有GBQ限制吗?谢谢.

Here is the SQL Statement. All other issues on this statement work. Is there a GBQ limitation on using LEFT JOINS and the "OR" operator? Thanks.

  SELECT 
  Source, 
  FirstName, 
  LastName,
  MiddleName, 
  Gender, 
  Age, 
  DOB, 
  Address, 
  Address2,
  City, 
  State, 
  Zip, 
  Zip4, 
  TimeZone, 
  Income, 
  HomeValue, 
  Networth, 
  MaritalStatus, 
  IsRenter, 
  HasChildren, 
  CreditRating, 
  Investor, 
  LinesOfCredit, 
  InvestorRealEstate, 
  Traveler, 
  Pets, 
  MailResponder, 
  Charitable, 
  PolicalDonations, 
  PoliticalParty, 
  coalesce(P.ATTOM_ID, T.ATTOM_ID) as ATTOM_ID,
  coalesce(P.GEOID, T.GEOID) as GEOID,
  Score,
  Score1,
  Score2,
  Score3,
  Score4,
  Score5,
  PropertyLatitude AS Latitude,
  PropertyLongitude AS Longitude
  FROM `db.ds.table1` P
 LEFT JOIN `db.ds.table2` T
ON 1 = 
CASE 
    WHEN (P.ATTOM_ID = T.ATTOM_ID) 
        THEN 1
    WHEN P.Zip = T. PropertyAddressZIP
            AND ( 
                    LOWER(P.LastName) = LOWER(T.DeedOwner1NameLast)
                    OR LOWER(P.LastName) = LOWER(T.PartyOwner1NameLast)
                )
            AND ( 
                    STRPOS(LOWER(P.Address), LOWER(T.PropertyAddressFull) ) > 0
                    OR STRPOS(LOWER(T.PropertyAddressFull), LOWER(P.Address) ) > 0 
                )
            AND IFNULL(T.PropertyAddressFull,'') != ''
        THEN 1
    ELSE 0 END

推荐答案

也许是另一种方法?是否可以在 JOIN 条件下拆分 OR ，一起创建两个 INNER JOIN 查询和 UNION ?

Maybe a different approach? Can you split the OR in your JOIN condition, create two INNER JOIN queries and UNION them together?

SELECT 
    Source, 
    FirstName, 
    LastName,
    MiddleName, 
    Gender, 
    Age, 
    DOB, 
    Address, 
    Address2,
    City, 
    State, 
    Zip, 
    Zip4, 
    TimeZone, 
    Income, 
    HomeValue, 
    Networth, 
    MaritalStatus, 
    IsRenter, 
    HasChildren, 
    CreditRating, 
    Investor, 
    LinesOfCredit, 
    InvestorRealEstate, 
    Traveler, 
    Pets, 
    MailResponder, 
    Charitable, 
    PolicalDonations, 
    PoliticalParty, 
    coalesce(P.ATTOM_ID, T.ATTOM_ID) as ATTOM_ID,
    coalesce(P.GEOID, T.GEOID) as GEOID,
    Score,
    Score1,
    Score2,
    Score3,
    Score4,
    Score5,
    PropertyLatitude AS Latitude,
    PropertyLongitude AS Longitude
FROM `db.ds.Table1` P
INNER JOIN `db.ds.Table2` T ON (P.ATTOM_ID = T.ATTOM_ID) 

UNION

SELECT 
    Source, 
    FirstName, 
    LastName,
    MiddleName, 
    Gender, 
    Age, 
    DOB, 
    Address, 
    Address2,
    City, 
    State, 
    Zip, 
    Zip4, 
    TimeZone, 
    Income, 
    HomeValue, 
    Networth, 
    MaritalStatus, 
    IsRenter, 
    HasChildren, 
    CreditRating, 
    Investor, 
    LinesOfCredit, 
    InvestorRealEstate, 
    Traveler, 
    Pets, 
    MailResponder, 
    Charitable, 
    PolicalDonations, 
    PoliticalParty, 
    coalesce(P.ATTOM_ID, T.ATTOM_ID) as ATTOM_ID,
    coalesce(P.GEOID, T.GEOID) as GEOID,
    Score,
    Score1,
    Score2,
    Score3,
    Score4,
    Score5,
    PropertyLatitude AS Latitude,
    PropertyLongitude AS Longitude
FROM `db.ds.Table1` P
INNER JOIN `db.ds.Table2` T ON P.Zip = T. PropertyAddressZIP
            AND ( 
                    LOWER(P.LastName) = LOWER(T.DeedOwner1NameLast)
                    OR LOWER(P.LastName) = LOWER(T.PartyOwner1NameLast)
                )
            AND ( 
                    STRPOS(LOWER(P.Address), LOWER(T.PropertyAddressFull) ) > 0
                    OR STRPOS(LOWER(T.PropertyAddressFull), LOWER(P.Address) ) > 0 
                )
            AND IFNULL(T.PropertyAddressFull,'') != ''

这篇关于大查询SELECT语句中的JOIN ERROR:使用LEFT JOIN的USING OR运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！