CODEWITHSUNDEEP
mysqlsqlfiltergoogle-bigquerywhere-clause
Chique_Code
Chique_Code

Reputation: 1530

where clause in sql with multiple conditions based on priorities SQL BigQuery

I have two tables that I am merging together in BigQuery. I need to filter the data based on multiple conditions. I need to return all the fields where url = https://www.mywebpage%, duration = 15000 and the third condition should grab either action = Midpoint or action = Complete. Below is the code:

SELECT
    d.duration,
    c.action,
    c.url
FROM
    (
        `table_action_url` c
        INNER JOIN `table_duration` d ON (d.id = c.id)
    )
WHERE c.url LIKE "https://www.mywebpage%" 
AND d.duration = '15000' 
AND c.action like 'Midpoint' 
OR (c.action like 'Complete')

This returns different fields for duration and url, but right actions. I also tried the following:

SELECT
    d.duration,
    c.action,
    c.url
FROM
    (
        `table_action_url` c
        INNER JOIN `table_duration` d ON (d.id = c.id)
    )
WHERE (c.url LIKE "https://www.mywebpage%" AND d.duration = '15000' AND c.action like 'Midpoint')
OR (c.url LIKE "https://www.mywebpage%" AND d.duration = '15000' AND c.action like 'Complete')

This query returns the right url and duration but for action, the values are all Complete. The desired output is:

duration      action      url 
15000        Midpoint     https://www.mywebpage
15000        Complete     https://www.mywebpage
15000        Midpoint     https://www.mywebpage

Upvotes: 1

Views: 8845

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269523

You need parentheses. Not in the FROM clause. In the WHERE clause:

FROM `table_action_url` c JOIN
     `table_duration` d
      ON d.id = c.id
WHERE c.url LIKE 'https://www.mywebpage%' AND
      d.duration = '15000' AND
     (c.action LIKE 'Midpoint' OR c.action LIKE 'Complete')

In your particular case, you can replace the LIKE with IN:

WHERE c.url LIKE 'https://www.mywebpage%' AND
      d.duration = '15000' AND
      c.action IN ('Midpoint', 'Complete')

But if you want wildcard matching, use regexp_contains() instead:

WHERE c.url LIKE 'https://www.mywebpage%' AND
      d.duration = '15000' AND
      REGEXP_CONTAINS(c.action, '^(Midpoint|Complete)$'

Upvotes: 4

jarlh
jarlh

Reputation: 44716

AND goes before OR...

You have:

WHERE (c.url LIKE "https://www.mywebpage%" 
       AND d.duration = '15000' 
       AND c.action like 'Midpoint')
OR (c.action like 'Complete')

But you want:

WHERE c.url LIKE "https://www.mywebpage%" 
AND d.duration = '15000' 
AND c.action in ('Midpoint', 'Complete')

Upvotes: 2

Related Questions