SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

double nine: Aug 8, 2013

I'm struggling with a beginner SQL question.

I have a two tables, basically it's got the following columns

- customer ID code
- purchase date
- store ID code
- Product ID
- invoice number

These are split into two tables: one with all purchases in the last 12 months, and one for the older purchases.

I need to get a list for all rows for a product A (say ID 123456) purchased since 20220101, if they haven't purchased product B (eg.987654)

what makes it extra fun is that these could be found in either table and I'm not getting there.
can someone help build a basic structure for this?

(e: specifically the filter "show if NOT PURCHASED 987654" is giving me trouble, all the rest is obvious

double nine fucked around with this message at 20:12 on Feb 16, 2023

# ¿ Feb 16, 2023 19:58

Adbot: ADBOT LOVES YOU

# ¿ May 17, 2024 02:17

double nine: Aug 8, 2013

thanks for the suggestions, I'll try them tomorrow.
I think it'll work

# ¿ Feb 16, 2023 21:30

double nine: Aug 8, 2013

TheFluff posted:

*a solution*

I've only been able to play with sql today, but I just want you to know that it works perfectly, thank you for solving the logic puzzle.

# ¿ Mar 14, 2023 18:42

double nine: Aug 8, 2013

this is really silly but I'm stumped with a null join clause

I have 2 tables. table 1 has client# and their subscription (1 line per subscription), the relevant start and end date. table two has products bought.

I need an qsl that returns basically everything from table2 (so a left join), but ONLY for people who are subscribed to subscription 24 AND NOT have subscription 01, both clauses true on the day of purchase.

I'm not even at the filter of the date yet, just that conditional filtering is stumping me.
eg

code:

table1
client   subs     start                 end
01234   01        01012023          31122999
01234   24        01012023          30042023                     
45235   24        01012000          31102015
98412   07        01072015          31122999
[...]

 

table2

client   prod    purcdate          amnt    invoice
01234   456312 27042022          266.95  98765423
45981   456999 14122023          012.99  89654351
39812   753942 13072023          140.44  89132186
45235   546832 25092023          045.99  218935931
[...]

# ¿ Feb 2, 2024 12:14

double nine: Aug 8, 2013

out of curiosity, is there a way to have an excel sql query refer to the content of a sheet?

for example, i have the following query:

code:

select clientid, tarifyear, this, that, and_the_other 
from database1.subdb2 t1
where t1.clientid = 123456789

and would like to have the query be dynamic so that the where statement becomes t1.clientid = table1.cellC3 or something to that effect

is this possible

# ¿ Feb 6, 2024 12:32

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'