Having Trouble with Sub-Queries

Question

Here is the question to the problem: "FR3.Q7: All views (all columns) that took place after October 15th, by Kindle devices, hosted by Yelp from cities where there are more than 10 clients. Also add the name of the client (as a first column) and city of the client (as a second column) for each view "

I get this far in my code

SELECT c.[Name]
      ,c.City
      ,v.ViewID
      ,v.ViewDate
      ,(v.ID)
      ,v.Device
      ,v.Browser
      ,v.Host
FROM [View] v JOIN Client c ON v.ID = c.ClientID
WHERE v.ViewDate >= '2019-10-16' AND v.Device LIKE '%kindle' AND v.Host LIKE '%yelp'
GROUP BY c.[Name],c.City,v.ViewID,v.ViewDate,v.ID,v.Device,v.Browser,v.Host

I get a result that is very close to the answer, however I do not have "Cities with at least 10 clients" filter in the result. No matter how I try to get this filter into the code above, it returns a blank table.

This code below successfully pulls up Cities with at least 10 clients in them separately, however I dont know how to get this code into the line of code above. I've tried sub-queries and derived tables but nothing works. Any help would be appreciated

SELECT City 
FROM Client
GROUP BY City
HAVING COUNT(ClientID) > 10

Add `where city in (your subquery with count)` – astentx Apr 29 '21 at 05:47 — astentx, Apr 29 '21 at 05:47
Or INNER JOIN it, or coordinate it within an EXISTS – Caius Jard Apr 29 '21 at 05:48 — Caius Jard, Apr 29 '21 at 05:48

Caius Jard · Answer 1 · 2021-04-29T05:52:20.227

Take your HAVING query there and join it in as a subquery

SELECT c.[Name]
      ,c.City
      ,v.ViewID
      ,v.ViewDate
      ,(v.ID)
      ,v.Device
      ,v.Browser
      ,v.Host
FROM [View] v JOIN Client c ON v.ID = c.ClientID

JOIN(SELECT City 
FROM Client
GROUP BY City
HAVING COUNT(ClientID) > 10) tenplus 
ON c.City = tenplus.city


WHERE v.ViewDate >= '2019-10-16' AND v.Device LIKE '%kindle' AND v.Host LIKE '%yelp'
GROUP BY c.[Name],c.City,v.ViewID,v.ViewDate,v.ID,v.Device,v.Browser,v.Host

I've deliberately left the formatting as a mess to outline how it is inserted, but that should be tidied up..

Basically, the subquery forms a list of city, and joining it in using an inner join will cause non qualifying cities to be discarded

You may not need the GroupBy on the outer query by the way; it groups everything and there are no aggregations in the select list. If you're using it to squish duplicates, check for join faults causing a Cartesian explosion (perhaps view has multiple identical records for the selected columns; aim to remove duplicates with a where clause rather than joining them and then deduping later)

Other ways mentioned in the comments:

SELECT c.[Name]
      ,c.City
      ,v.ViewID
      ,v.ViewDate
      ,(v.ID)
      ,v.Device
      ,v.Browser
      ,v.Host
FROM [View] v JOIN Client c ON v.ID = c.ClientID
WHERE v.ViewDate >= '2019-10-16' AND v.Device LIKE '%kindle' AND v.Host LIKE '%yelp'

AND c.city IN(SELECT City 
FROM Client
GROUP BY City
HAVING COUNT(ClientID) > 10)

GROUP BY c.[Name],c.City,v.ViewID,v.ViewDate,v.ID,v.Device,v.Browser,v.Host

SELECT c.[Name]
      ,c.City
      ,v.ViewID
      ,v.ViewDate
      ,(v.ID)
      ,v.Device
      ,v.Browser
      ,v.Host
FROM [View] v JOIN Client c ON v.ID = c.ClientID
WHERE v.ViewDate >= '2019-10-16' AND v.Device LIKE '%kindle' AND v.Host LIKE '%yelp'

AND EXISTS(SELECT null 
FROM Client x
WHERE x.city = c.city
GROUP BY City
HAVING COUNT(ClientID) > 10)

GROUP BY c.[Name],c.City,v.ViewID,v.ViewDate,v.ID,v.Device,v.Browser,v.Host

These days they will probably all work identically

score 0 · Answer 2 · answered Apr 29 '21 at 12:54

Use window functions to count the clients in a city:

SELECT c.[Name], c.City, v.ViewID, v.ViewDate, v.ID,
       v.Device, v.Browser, v.Host
FROM [View] v JOIN
     (SELECT c.*, COUNT(*) OVER (PARTITION BY city) as cnt_city
      FROM Client c 
     ) c
     ON v.ID = c.ClientID
WHERE v.ViewDate >= '2019-10-16' AND
      v.Device LIKE '%kindle' AND
      v.Host LIKE '%yelp' AND
      c.cnt_city >= 10
GROUP BY c.[Name], c.City, v.ViewID, v.ViewDate, v.ID, v.Device, v.Browser, v.Host ;

Note: I wonder if you really need the GROUP BY. That is a rather expensive operation. Are the duplicates created by the JOIN? Or are they in the view? Or both?

You don't provide an explanation of the data. You might want to ask another question with sample data and desired results.

Having Trouble with Sub-Queries

2 Answers2