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'

hey mom its 420: May 12, 2007

My SQL is a little rusty but here's a shot at it

code:

SELECT recipient.name, author.name, messages.message
FROM users AS recipient
INNER JOIN messages ON messages.recipient = recipient.id
INNER JOIN users AS author ON author.id = messages.author
WHERE recipient.id = [the id of the guy who is looking at his messages]

# ¿ Nov 21, 2007 11:42

Adbot: ADBOT LOVES YOU

# ¿ Apr 29, 2024 07:39

hey mom its 420: May 12, 2007

I don't know about the queries not running, it's best to just see what the SQL error messages tell you and act on that. But as for the slow queries, have you created indices everywhere where they used to be? Because if, say, your foreign keys aren't indexed the DBMS will be doing full table scans on joins.

# ¿ Nov 23, 2007 10:56

hey mom its 420: May 12, 2007

You can see how efficient your query is. How much records needed to be traversed to get the results of the query. For instance, you almost never want to do full table scans. It's good to check your queries with explain to see if full table scans are happening at certain queries so you can introduce indices or reform the queries.

# ¿ Dec 4, 2007 20:10

hey mom its 420: May 12, 2007

yo what's up YOSPOS long time no see.

I have to get some data out of a few weirdly designed tables in postgres. We process tips for hairdressers and whatnot. We have a Ledger table where we track positive and negative additions to someone's balance. There's also a Payout table, and a payout is us sending money out, and a payout covers several tips, for several people. Once someone has been paid out, we add negative balance to their Ledger and mark it with a payoutId to know which payout resulted in that person's balance going down.

So far so good. I'm writing some analytics for that cause the db guy is not currently available. I have to get the average number of tips per payout for one person. Now you would imagine that there would be a M2M table or something that tells you which tips were paid out in which payout. But there isn't. Apparently, the way to get all the Ledger entries that were paid out in one payout (say payoutId = 99) is to find the Ledger entry with payoutId = 98 and then just go up by ids until we get to a Ledger entry with payoutId = 99.

so I have

code:

Ledger or whatever
id    reason    amount    payoutId
9     tip       $4
8     payout    -$32      9
7     tip       $4
6     tip       $4
5     tip       $4
4     payout    -$52      8
3     tip       $4
2     payout    $3        7

And I need to get a result that tells me that payout 9 had 3 tips, payout 7 had 1 tip and so on for each payout. Or even better to annotate it with a column that says which payout the tip was paid out, then I can do grouping and aggreagates. Getting this would be pretty easy if the data were more normalized or If i were doing this in application code. but with sql this is just hosed

# ¿ Jul 24, 2020 15:19

hey mom its 420: May 12, 2007

Thanks for the responses. Yeah getting them only for one payout would be doable, just limit the ids to be between two payoutIds. However, I need aggregate data for all of them. I'll look into stored procedures and window function, it's my intuition that this can't be done with just normal SQL because it looks like you need something akin to a map reduce to do it.

# ¿ Jul 26, 2020 13:02

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