CODEWITHSUNDEEP
google-bigquery
rbkk2016
rbkk2016

Reputation: 215

How to select specific records from a BigQuery table?

I have to perform the below logic. I have a table with several fields and from that table I have to select prod that has group as c but not d. Some prods will have group as c as well as d. And from the selected prods, I have to select other fields such as latitude, longitude etc.

From the below table, using SQL, how do I retrieve the highlighted rows?

enter image description here

Upvotes: 0

Views: 1387

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173210

My understanding that by "c" you meant "i" and by "d" you meant "f"
Below does this.
It identifies those Prod's that are in "i" group but not in "f" group 

SELECT Prod
FROM (
  SELECT Prod, 
    CONCAT('|', GROUP_CONCAT_UNQUOTED(group, '|'), '|') AS list 
  FROM YourTable
  GROUP BY Prod
)
WHERE list CONTAINS '|i|' 
AND NOT list CONTAINS '|f|'

You can test this with below 

SELECT Prod
FROM (
  SELECT 
    Prod, 
    CONCAT('|', GROUP_CONCAT_UNQUOTED(group, '|'), '|') AS list 
  FROM 
    (SELECT 'prod1' AS Prod, 'i' AS group),
    (SELECT 'prod1' AS Prod, 'f' AS group),
    (SELECT 'prod1' AS Prod, 'f' AS group),
    (SELECT 'prod2' AS Prod, 'i' AS group),
    (SELECT 'prod3' AS Prod, 'i' AS group),
    (SELECT 'prod4' AS Prod, 'i' AS group),
    (SELECT 'prod4' AS Prod, 'f' AS group),
    (SELECT 'prod4' AS Prod, 'f' AS group),
    (SELECT 'prod4' AS Prod, 'f' AS group),
  GROUP BY Prod
)
WHERE list CONTAINS '|i|' 
AND NOT list CONTAINS '|f|'

so you final query can look something like 

SELECT <list of needed fields>
FROM YourTable
WHERE Prod IN (
  SELECT Prod
  FROM (
    SELECT Prod, 
      CONCAT('|', GROUP_CONCAT_UNQUOTED(group, '|'), '|') AS list 
    FROM YourTable
    GROUP BY Prod
  )
  WHERE list CONTAINS '|i|' 
  AND NOT list CONTAINS '|f|'
)

Upvotes: 1

Graham Polley
Graham Polley

Reputation: 14791

By using the most basic of SQL i.e. use WHERE:

SELECT <the_cols_you_want> FROM [<Dataset.Table>] WHERE Prod in ('prod2','prod3') and Group = 'i'

Upvotes: 0

Related Questions