|
I've got another nice and stupid question. I'm clearly being a moron but not seeing how. I am trying to create 2 tables. Bank(BankID*, BankName) Accounts(AccountID*, BankID, AccountNumber, Balance) I tried to use the SequelPro Relations View window and failed so moved to just typing in the SQL. SQL code:
quote:Cannot find an index in the referenced table where the If I change FOREIGN KEY (`AccountID`) then it works just fine, but that's surely not what I'm trying to do?
|
# ? Jun 2, 2018 19:42 |
|
|
# ? Jun 8, 2024 06:00 |
|
I don't know anything about InnoDB, but perhaps BankID/ID both need to be the same datatype? It's defined as an INT(3) in Accounts and as an unsigned INT(3) in Banks.
|
# ? Jun 2, 2018 19:54 |
|
Tax Oddity posted:I don't know anything about InnoDB, but perhaps BankID/ID both need to be the same datatype? It's defined as an INT(3) in Accounts and as an unsigned INT(3) in Banks. Fixed. Thank you greatly!
|
# ? Jun 2, 2018 20:14 |
|
I'd like to make the content of a text column searchable in an SQLite table. Should i just do: SQL code:
|
# ? Jun 7, 2018 12:13 |
|
I've got the following tables: NDC20180529, with column NDC DrugList, with columns DrugListID, DrugListName, and ActiveFlag DrugListNDC, with columns DrugListID, NDC, and ExpirationDate I want to take all rows from DrugList and DrugListNDC with a given DrugListName, then on every row on those tables for which the NDC value doesn't exist in the NDC20180529 table, I want to update the ActiveFlag and ExpirationDate values. I've been told to use the T-SQL MERGE statement here, and I don't think it's possible. I'm going to just do a standard SELECT/UPDATE instead, but is it possible to use MERGE here?
|
# ? Jun 12, 2018 19:23 |
|
anthonypants posted:I've got the following tables: MERGE can have a CTE where you can do whatever crazy join you want and use the output set to merge with your target tables, but you would need two merge statements, I think SQL code:
|
# ? Jun 12, 2018 19:58 |
|
There is no way in MSSQL that I am aware of to update two tables with a single query. You can use views and CTE's that reference multiple tables as the target of both MERGE and UPDATE, but in both cases the actual update operation will only succeed if it references columns in only one of the underlying tables. So, yes, two merge queries. Or instead of a merge, just use a regular update:SQL code:
TheFluff fucked around with this message at 20:31 on Jun 12, 2018 |
# ? Jun 12, 2018 20:25 |
|
Yeah, you have to have two statements to update two tables, and if you're just doing an update, as opposed to update or insert depending on some logic, then you gain nothing by doing a merge (and in fact the Docs say you can negatively affect performance by doing a merge when just a straight update would do).
|
# ? Jun 13, 2018 14:21 |
|
Is there a more succinct way of expressing this?SQL code:
|
# ? Jun 14, 2018 14:31 |
|
Munkeymon posted:Is there a more succinct way of expressing this? Your JOIN predicate is 50 LINES? Are you doing a poor man's Cartesian join or something?
|
# ? Jun 14, 2018 14:39 |
|
do you need a FULL JOIN maybe?
|
# ? Jun 14, 2018 14:47 |
|
Nth Doctor posted:Your JOIN predicate is 50 LINES? Are you doing a poor man's Cartesian join or something? Each one is about 25 lines, but a lot of it is nesting ANDs and ORs. Whole thing is a workaround for unreliable data, but the point here is that, even if it wasn't, I'm not sure how else to express that I want a result set that contains two rows per match, not one row of A.*, B.*
|
# ? Jun 14, 2018 15:23 |
|
Munkeymon posted:Each one is about 25 lines, but a lot of it is nesting ANDs and ORs. Whole thing is a workaround for unreliable data, but the point here is that, even if it wasn't, I'm not sure how else to express that I want a result set that contains two rows per match, not one row of A.*, B.* Depending on the exact way you write the conditions, maybe you could do something like: SQL code:
If your duplicate criteria was a bit simpler (and your RDBMS supports window functions), you could also do something like: SQL code:
|
# ? Jun 14, 2018 16:26 |
|
Jethro posted:Just to clarify, you have one table that has duplicate records in it, but the exact definition of "duplicate" is complicated? Conceptually, yeah, but in reality, it's the result set of a multi-table join. quote:Depending on the exact way you write the conditions, maybe you could do something like: quote:If your duplicate criteria was a bit simpler (and your RDBMS supports window functions), you could also do something like: It sure does, and that's the kind of trick I was hoping for.
|
# ? Jun 14, 2018 16:50 |
|
Jethro posted:Just to clarify, you have one table that has duplicate records in it, but the exact definition of "duplicate" is complicated? Sometime I really ought to immerse myself in the syntax of window functions, but I know they're there, but I also know MS Books Online exists.
|
# ? Jun 14, 2018 17:37 |
|
I can write SQL that gives me the expected output but I still don’t know what the gently caress it’s actually doing. Am I missing an undergrad class’s worth of stuff?
|
# ? Jun 14, 2018 20:13 |
|
I don't think they teach how a relational database server actually works in undergrad classes. They might cover B-tree indexes and some set theory though, I guess? Personally I found SQL Server Execution Plans, 2nd Edition to be pretty interesting when it came to trying to understand what the query planner actually ends up doing to deliver your goods. It's written for MSSQL but I figure the basic stuff is probably similar in most SQL databases.
|
# ? Jun 14, 2018 20:51 |
|
Bobcats posted:I can write SQL that gives me the expected output but I still don’t know what the gently caress it’s actually doing. Probably not. I got my degree in CS from an engineering school and had to take a databases class, and from a practical usage standpoint the class was hot flaming garbage. As part of the schools co-op program I had been doing SQL for three years already and the practical stuff I used on a daily basis looked nothing like what was being taught.
|
# ? Jun 14, 2018 22:17 |
|
I have the log files from my Devastation Quake 2 game server and I am trying to calculate the score per player per match given some specific events for a stats page that I'm putting together. given this data: code:
I imagine it's some kind of CASE statement, but I'm confused how to output the summed score into a variable that I can display.
|
# ? Jun 15, 2018 20:44 |
|
If I'm on MSSQL, and I made an UPDATE but forgot to make my WHERE clause specific enough, but I also included a BEGIN TRAN at the beginning of the query, would I be able to stop the query and run a ROLLBACK, or would I have to wait for the query to finish before the ROLLBACK would be successful?
|
# ? Jun 15, 2018 21:00 |
|
Agrikk posted:I have the log files from my Devastation Quake 2 game server and I am trying to calculate the score per player per match given some specific events for a stats page that I'm putting together. SELECT killername, SUM(CASE WHEN mode = 'DEV' AND killertype = 'DEVCG' AND victimtype = 'GRUNT' THEN 1 WHEN... (other score situations) END) AS Score FROM Source GROUP BY killername
|
# ? Jun 15, 2018 21:07 |
|
Agrikk posted:I have the log files from my Devastation Quake 2 game server and I am trying to calculate the score per player per match given some specific events for a stats page that I'm putting together. As far as SQL is concerned you want something like SELECT SUM(CASE ... END) AS score FROM your_table GROUP BY killername. Putting it into a variable (in a scripting language?) isn't SQL strictly speaking but that doesn't mean you can't ask about it here. But you are going to need to be more specific about where you're running into difficulties.
|
# ? Jun 15, 2018 21:07 |
|
anthonypants posted:If I'm on MSSQL, and I made an UPDATE but forgot to make my WHERE clause specific enough, but I also included a BEGIN TRAN at the beginning of the query, would I be able to stop the query and run a ROLLBACK, or would I have to wait for the query to finish before the ROLLBACK would be successful?
|
# ? Jun 15, 2018 22:35 |
|
I mean, I did this in a development environment and not in production, so it's more of a fun thought experiment than me being panicky. Just wondering if I didn't have to wait until this query finished:code:
|
# ? Jun 15, 2018 22:46 |
|
If you're using a database where a transaction containing an incomplete or failed statement can actually be committed, you need to switch databases.
|
# ? Jun 15, 2018 23:49 |
|
PhantomOfTheCopier posted:If you're using a database where a transaction containing an incomplete or failed statement can actually be committed, you need to switch databases. If I'm doing a large UPDATE query, and I cancel out in the middle of it, are any rows updated? If any of those rows are updated, can they be rolled back? Will the ROLLBACK command error out if the transaction it wants to roll back was interrupted halfway?
|
# ? Jun 15, 2018 23:55 |
|
anthonypants posted:Okay, let me rephrase the question. if there is any error during the update (including an interruption), no rows are updated. so your rollback will work fine
|
# ? Jun 16, 2018 00:08 |
|
I'm still learning the basics of SQL Server and need a little guidance on a problem: I have a table of hospital admissions and I'm joining it to a table of lab procedures. I end up with something like this: code:
code:
Hughmoris fucked around with this message at 05:14 on Jun 19, 2018 |
# ? Jun 19, 2018 04:50 |
|
Hughmoris posted:I'm still learning the basics of SQL and need a little guidance on a problem: code:
anthonypants fucked around with this message at 05:06 on Jun 19, 2018 |
# ? Jun 19, 2018 04:59 |
|
anthonypants posted:Dunno what flavor of SQL you're in but you're probably looking at a Common Table Expression here. Something like this: Thanks for taking the time to type that up. I'm using SQL Server, and am starting to explore CTEs. I believe I see how your solution is working, and will give it a go in the morning. Thanks again! Hughmoris fucked around with this message at 05:25 on Jun 19, 2018 |
# ? Jun 19, 2018 05:13 |
|
anthonypants posted:Dunno what flavor of SQL you're in but you're probably looking at a Common Table Expression here. Something like this: This won't quite do it, it still produces all the rows. You'd want something like: code:
|
# ? Jun 19, 2018 08:00 |
|
When you see pattern involving a duplicating join and a complex oddball join condition like "get the first N rows ordered by..." or "get some complex aggregate of the right hand table" or something similar, and you go "I'm gonna have to write a really slow and obnoxious subquery here", a better solution is frequently a lateral join (CROSS APPLY/OUTER APPLY in SQL Server parlance). Postgres and MSSQL have them, but I dunno about anything else. In this case, it'd look something like this:SQL code:
e: actually, in this case the CTE solution with "select min(lab_time)" might actually be better, or that's what a quick test indicates. Probably depends on indexes though. I have a lot to learn about what's appropriate when, still. TheFluff fucked around with this message at 11:06 on Jun 19, 2018 |
# ? Jun 19, 2018 10:49 |
|
As much as I love window functions and other fancy stuff, I would think a simple subquery (or CTE if you like) with a group by that gets both min times would be the way to go.SQL code:
|
# ? Jun 19, 2018 14:35 |
|
Thanks all for the earlier replies. It helped me frame my thinking. Now: I'm trying to count kills and deaths per player given the following: I have one table (Players) that simply lists all of the people who have ever received a kill or a death on my server: PlayerName ____________ PlayerA PlayerB PlayerC ... I have another table (KillInfo) having KillID Killer Victim ------------------------------------ 0001 PlayerA PlayerB 0002 PlayerB PlayerA 0003 PlayerC PlayerA I'm looking to produce a stats table: PlayerName Kills Deaths --------------------------------------------- PlayerA 1 2 PlayerB 1 1 PlayerC 1 0 I figured I left join against Players.PlayerName for KillInfo.Kills and KillInfo.Deaths code:
|
# ? Jun 19, 2018 21:23 |
|
This should do it, aggregate your two subtotals separately and then join them on afterwards. code:
|
# ? Jun 19, 2018 22:58 |
|
Moonwolf posted:This should do it, aggregate your two subtotals separately and then join them on afterwards. Well drat. It's literally the same flow as using two temp tables but instead it's sub queries being joined. I feel dumb now. (Thank you.)
|
# ? Jun 19, 2018 23:12 |
|
The reason your other way doesn't work is, having done it on the actually super useful Oracle livesql (https://livesql.oracle.com) is hard to show because the data explodes out, but with 5 players, who each kill each other once, you end up with 150 rows with joining for kills and deaths at the same time. You get for each kill row, every possible death row, because they're independent rows joined in. Link to worksheet so you can play with it: https://livesql.oracle.com/apex/livesql/s/guwqntt2shpfia6s7u1fbb5d3 Oracle's accounts are free for this. Edit: They're expensive for everything though. Also, on the topic of your query, if you have it, COALESCE or NVL would save you needing the players table, you can join the two subqueries like so: code:
Moonwolf fucked around with this message at 00:03 on Jun 20, 2018 |
# ? Jun 19, 2018 23:52 |
|
quote:Hospital Lab SQL stuff... Thanks everyone for the tips, I was able to get the results I needed.
|
# ? Jun 20, 2018 01:57 |
|
Jethro posted:As much as I love window functions and other fancy stuff, I would think a simple subquery (or CTE if you like) with a group by that gets both min times would be the way to go. This is the correct way to approach that patient data problem imo. Conditional aggregates.
|
# ? Jun 20, 2018 13:46 |
|
|
# ? Jun 8, 2024 06:00 |
|
What's a good book or other resource on practical SQL? Preferably something that doesn't spend eons of time on how to do basic poo poo that every programmer who has to touch any SQL already knows how to do...
|
# ? Jun 20, 2018 18:51 |