Cross joining 3 columns and need to eliminate null rows if null across certain conditions-CodePudding

Ok, this is kind of hard to explain, but I'll do my best here. I'm crossing joining on 3 columns and need to eliminate certain rows so that the results aren't excessively large. I think this is best explained with a fiddle.

I have two tables:

ticks(dait DATE, tick INT);

votes(dayt DATE, tick INT, user INT, vote INT);

And I need to cross join them on dait, tick, user. Here's where the fiddle is helpful.

https://www.db-fiddle.com/f/9phZ7EpRUS4FNRBZRdSYZa/29

And here's the query I've tried that's gotten me the closest:

SELECT x.user, ticks.tick, dait, vote, ROW_NUMBER() OVER (ORDER BY x.user, ticks.tick, 
dait) AS ilocat
FROM ticks
CROSS JOIN (SELECT DISTINCT dayt, user, tick FROM votes) x
LEFT JOIN votes
ON votes.dayt = ticks.dait AND votes.tick = ticks.tick AND x.user = votes.user
GROUP BY dait, ticks.tick, x.user
ORDER BY x.user, ticks.tick, dait;

You can see user 12 has only voted once on tick 3 on 2021-10-27. The output of the fiddle is very close to what I want BUT I need to eliminate rows (ilocat) 19-24 that show all null votes for ticks 1 and 2 from user 12 across the date range.

In other words, if a user has never voted on a tick, I do not want to return those rows. BUT, if a user has voted at least once on a tick, then I want to return all rows corresponding to all dates (and show null votes on the days the user didn't vote).

The reason why I need to see all the dates the user didn't vote is because I am performing later queries to find the streaks of the user over each tick they've voted on. As you can imagine, the query I've tried will yield very large results if I am unable to eliminate those unnecessary rows.

CodePudding user response：

if a user has never voted on a tick, I do not want to return those rows. BUT, if a user has voted at least once on a tick, then I want to return all rows corresponding to all dates

You should change the CROSS join to an INNER join:

SELECT x.user, t.tick, t.dait, v.vote, 
       ROW_NUMBER() OVER (ORDER BY x.user, t.tick, dait) AS ilocat
FROM ticks t
INNER JOIN (SELECT DISTINCT dayt, user, tick FROM votes) x
ON t.tick = x.tick
LEFT JOIN votes v
ON v.dayt = t.dait AND v.tick = t.tick AND x.user = v.user
GROUP BY dait, t.tick, x.user
ORDER BY x.user, t.tick, t.dait;

See the demo.