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'

«‹›303 »

Sad Panda: Sep 22, 2004; I'm a Sad Panda.

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:

CREATE TABLE `Banks` (
  `ID` int(3) unsigned NOT NULL AUTO_INCREMENT,
  `BankName` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `Accounts` (
  `AccountID` int(3) unsigned NOT NULL AUTO_INCREMENT,
  `BankID` int(3) NOT NULL,
  PRIMARY KEY (`AccountID`),
  FOREIGN KEY (`BankID`) REFERENCES `Banks` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This throws

quote:

Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.

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

Adbot: ADBOT LOVES YOU

# ? Jun 4, 2024 18:25

Tax Oddity: Apr 8, 2007; The love cannon has a very short range, about 2 feet, so you're inevitably out of range. I have to close the distance.

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

Sad Panda: Sep 22, 2004; I'm a Sad Panda.

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

unpacked robinhood: Feb 18, 2013; by Fluffdaddy

I'd like to make the content of a text column searchable in an SQLite table.

Should i just do:

SQL code:

CREATE VIRTUAL TABLE TableName using FTS5(column1 typeA, column2 typeA etc);

And then insert and search normally ? I think I get hanged up on the 'virtual' keyword.

# ? Jun 7, 2018 12:13

anthonypants: May 6, 2007; by Nyc_Tattoo; Dinosaur Gum

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

Munkeymon: Aug 14, 2003; Motherfucker's got an
armor-piercing crowbar! Rigoddamndicu𝜆ous.

anthonypants posted:

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?

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:

;WITH MissingNDC AS (
  SELECT DLNDC.NDC
  FROM DrugList as DL
    inner join DrugListNDC as DLNDC on DL.DrigListId = DLNDC.DrugListId
    left outer join NDC20180529 as NDC on DLNDC.NDC = NDC.NDC
  WHERE DL.DrugName = 'Phartaphil' AND NDC.NDC is null
)
MERGE INTO DrugListNDC as DLNDC
USING MissingNDC on DLNDC.NDC = MissingNDC
WHEN matched THEN UPDATE SET ExpirationDate = GETDATE()

off the top of my head, may not even be valid

# ? Jun 12, 2018 19:58

TheFluff: Dec 13, 2006; FRIENDS, LISTEN TO ME
I AM A SEAGULL
OF WEALTH AND TASTE

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:

UPDATE DrugList
SET ActiveFlag = 1
FROM DrugList
WHERE DrugList.DrugName = N'Funny name' AND NOT EXISTS (
  SELECT *
  FROM DrugListNDC
  INNER JOIN NDC20180529 ON (NDC20180529.NDC = DrugListNDC.NDC)
  WHERE DrugListNDC.DrugListID = DrugList.DrugListID
)

TheFluff fucked around with this message at 20:31 on Jun 12, 2018

# ? Jun 12, 2018 20:25

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

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

Munkeymon: Aug 14, 2003; Motherfucker's got an
armor-piercing crowbar! Rigoddamndicu𝜆ous.

Is there a more succinct way of expressing this?

SQL code:

select *
from (
    select A.*
    from SomeTable as A
      join SomeTable as B on A.whatever = B.whatever
       /* the join criteria is actually quite complicated, is the thing */
  union all
    select B.*
    from SomeTable as A
      join SomeTable as B on A.whatever = B.whatever
       /* so this whole thing ends up being ~50 lines of mostly copy+paste */
) as Duplicates

# ? Jun 14, 2018 14:31

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

Munkeymon posted:

Is there a more succinct way of expressing this?

SQL code:

select *
from (
    select A.*
    from SomeTable as A
      join SomeTable as B on A.whatever = B.whatever
       /* the join criteria is actually quite complicated, is the thing */
  union all
    select B.*
    from SomeTable as A
      join SomeTable as B on A.whatever = B.whatever
       /* so this whole thing ends up being ~50 lines of mostly copy+paste */
) as Duplicates

Your JOIN predicate is 50 LINES? Are you doing a poor man's Cartesian join or something?

# ? Jun 14, 2018 14:39

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

do you need a FULL JOIN maybe?

# ? Jun 14, 2018 14:47

Munkeymon: Aug 14, 2003; Motherfucker's got an
armor-piercing crowbar! Rigoddamndicu𝜆ous.

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

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

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.*

Just to clarify, you have one table that has duplicate records in it, but the exact definition of "duplicate" is complicated?

Depending on the exact way you write the conditions, maybe you could do something like:

SQL code:

Select A.*
  from SomeTable as A
 where exists (select 0 from SomeTable as B
                where A.whatever = B.whatever
                      /*your 25 lines of criteria*/
                      and A.record_id <> B.record_id /*or whatever method you use to only get duplicates instead of matching up a record to itself*/
                      )

But that depends on you being able to write your conditions such that it didn't matter whether a record was in A or B in your original query. Assuming you can do that, then this method does let you only write the criteria once.

If your duplicate criteria was a bit simpler (and your RDBMS supports window functions), you could also do something like:

SQL code:

Select *
  from (
Select A.*,
       count(*) over (partition by key_column_1, key_column_2 /*etc.*/) dup_ct
  from SomeTable as A
) as Duplicates
where dup_ct > 1

# ? Jun 14, 2018 16:26

Munkeymon: Aug 14, 2003; Motherfucker's got an
armor-piercing crowbar! Rigoddamndicu𝜆ous.

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:
SQL code:
Select A.*
  from SomeTable as A
 where exists (select 0 from SomeTable as B
                where A.whatever = B.whatever
                      /*your 25 lines of criteria*/
                      and A.record_id <> B.record_id /*or whatever method you use to only get duplicates instead of matching up a record to itself*/
                      )
But that depends on you being able to write your conditions such that it didn't matter whether a record was in A or B in your original query. Assuming you can do that, then this method does let you only write the criteria once.

A and B are the same table/result set in my case, so yeah that might work if it's not slower.

quote:

If your duplicate criteria was a bit simpler (and your RDBMS supports window functions), you could also do something like:
SQL code:
Select *
  from (
Select A.*,
       count(*) over (partition by key_column_1, key_column_2 /*etc.*/) dup_ct
  from SomeTable as A
) as Duplicates
where dup_ct > 1

It sure does, and that's the kind of trick I was hoping for.

# ? Jun 14, 2018 16:50

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

Jethro posted:

Just to clarify, you have one table that has duplicate records in it, but the exact definition of "duplicate" is complicated?

Depending on the exact way you write the conditions, maybe you could do something like:
SQL code:
Select A.*
  from SomeTable as A
 where exists (select 0 from SomeTable as B
                where A.whatever = B.whatever
                      /*your 25 lines of criteria*/
                      and A.record_id <> B.record_id /*or whatever method you use to only get duplicates instead of matching up a record to itself*/
                      )
But that depends on you being able to write your conditions such that it didn't matter whether a record was in A or B in your original query. Assuming you can do that, then this method does let you only write the criteria once.

If your duplicate criteria was a bit simpler (and your RDBMS supports window functions), you could also do something like:
SQL code:
Select *
  from (
Select A.*,
       count(*) over (partition by key_column_1, key_column_2 /*etc.*/) dup_ct
  from SomeTable as A
) as Duplicates
where dup_ct > 1

Sometime I really ought to immerse myself in the syntax of window functions, but :effort:

I know they're there, but I also know MS Books Online exists.

# ? Jun 14, 2018 17:37

Bobcats: Aug 5, 2004; Oh

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

TheFluff: Dec 13, 2006; FRIENDS, LISTEN TO ME
I AM A SEAGULL
OF WEALTH AND TASTE

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

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

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.

Am I missing an undergrad class’s worth of stuff?

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

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

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:

# mode, killername, killertype, victimtype
'DEV', '0megaMan[APE]', 'DEVCG', 'GRUNT'
'ROCKET', 'Lag', 'GRUNT', 'GRUNT'
'R_SPLASH', 'Lag', 'GRUNT', 'DEVCG'
'RAILGUN', 'Entrails<VP>', 'GRUNT', 'GRUNT'
'DEVCRUSH', 'Entrails<VP>', 'DEVCG', 'GRUNT'
'DEV', 'Entrails<VP>', 'DEVCG', 'DEVRL'
'ROCKET', 'Lag', 'GRUNT', 'GRUNT'
'DEV', '0megaMan[APE]', 'DEVRL', 'GRUNT'
'FALLING', '0megaMan[APE]', 'DEVRL', 'DEVRL'

Scoring is based on combinations of mode, killertype and victimtype, with some kills worth 5,2,1,-1,-5 points depending on that combination.

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

anthonypants: May 6, 2007; by Nyc_Tattoo; Dinosaur Gum

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

Kuule hain nussivan: Nov 27, 2008

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.

given this data:
code:
# mode, killername, killertype, victimtype
'DEV', '0megaMan[APE]', 'DEVCG', 'GRUNT'
'ROCKET', 'Lag', 'GRUNT', 'GRUNT'
'R_SPLASH', 'Lag', 'GRUNT', 'DEVCG'
'RAILGUN', 'Entrails<VP>', 'GRUNT', 'GRUNT'
'DEVCRUSH', 'Entrails<VP>', 'DEVCG', 'GRUNT'
'DEV', 'Entrails<VP>', 'DEVCG', 'DEVRL'
'ROCKET', 'Lag', 'GRUNT', 'GRUNT'
'DEV', '0megaMan[APE]', 'DEVRL', 'GRUNT'
'FALLING', '0megaMan[APE]', 'DEVRL', 'DEVRL'
Scoring is based on combinations of mode, killertype and victimtype, with some kills worth 5,2,1,-1,-5 points depending on that combination.

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.

I'm not 100% sure what you're going for, but maybe something like...

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

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

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.

given this data:
code:
# mode, killername, killertype, victimtype
'DEV', '0megaMan[APE]', 'DEVCG', 'GRUNT'
'ROCKET', 'Lag', 'GRUNT', 'GRUNT'
'R_SPLASH', 'Lag', 'GRUNT', 'DEVCG'
'RAILGUN', 'Entrails<VP>', 'GRUNT', 'GRUNT'
'DEVCRUSH', 'Entrails<VP>', 'DEVCG', 'GRUNT'
'DEV', 'Entrails<VP>', 'DEVCG', 'DEVRL'
'ROCKET', 'Lag', 'GRUNT', 'GRUNT'
'DEV', '0megaMan[APE]', 'DEVRL', 'GRUNT'
'FALLING', '0megaMan[APE]', 'DEVRL', 'DEVRL'
Scoring is based on combinations of mode, killertype and victimtype, with some kills worth 5,2,1,-1,-5 points depending on that combination.

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.

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

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

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

anthonypants: May 6, 2007; by Nyc_Tattoo; Dinosaur Gum

Nth Doctor posted:

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:

(4102956 rows affected)

# ? Jun 15, 2018 22:46

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

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

anthonypants: May 6, 2007; by Nyc_Tattoo; Dinosaur Gum

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.

Okay, let me rephrase the question.
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

DELETE CASCADE: Oct 25, 2017; i haven't washed my penis since i jerked it to a phtotograph of george w. bush in 2003

anthonypants posted:

Okay, let me rephrase the question.
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?

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

Hughmoris: Apr 21, 2007; Let's go to the abyss!

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:

patient, admission_time, lab_type, lab_time

john doe, 5/24/2018 10:00, ekg, 5/24/2018 10:15
john doe, 5/24/2018 10:00, ekg, 5/26/2018 23:00
john doe, 5/24/2018 10:00, mri, 5/24/2018 11:28
john doe, 5/24/2018 10:00, mri, 5/27/2018 14:50
sally field, 5/17/2018 09:40, ekg, 5/17/2018 09:50
sally field, 5/17/2018 09:40, ekg, 5/21/2018 13:22
sally field, 5/17/2018 09:40, mri, 5/17/2018 16:10
....

I'd like to end up with is a table that shows the patient, their admission time, and the first time they received an ekg and mri.

code:

patient, admission_time, 1st_ekg_time, 1st_mri_time

john doe, 5/24/2018 10:00, 5/24/2018 10:15, 5/24/2018 11:28
sally field, 5/17/2018 09:40, 5/17/2018 09:50, 5/17/2018 16:10
...

What is a good way to approach this?

Hughmoris fucked around with this message at 05:14 on Jun 19, 2018

# ? Jun 19, 2018 04:50

anthonypants: May 6, 2007; by Nyc_Tattoo; Dinosaur Gum

Hughmoris posted:

I'm still learning the basics of SQL 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:
patient, admission_time, lab_type, lab_time

john doe, 5/24/2018 10:00, ekg, 5/24/2018 10:15
john doe, 5/24/2018 10:00, ekg, 5/26/2018 23:00
john doe, 5/24/2018 10:00, mri, 5/24/2018 11:28
john doe, 5/24/2018 10:00, mri, 5/27/2018 14:50
sally field, 5/17/2018 09:40, ekg, 5/17/2018 09:50
sally field, 5/17/2018 09:40, ekg, 5/21/2018 13:22
sally field, 5/17/2018 09:40, mri, 5/17/2018 16:10
....
I'd like to end up with is a table that shows the patient, their admission time, and the first time they received an ekg and mri.
code:
patient, admission_time, 1st_ekg_time, 1st_mri_time

john doe, 5/24/2018 10:00, 5/24/2018 10:15, 5/24/2018 11:28
sally field, 5/17/2018 09:40, 5/17/2018 09:50, 5/17/2018 16:10
...
What is a good way to approach this?

Dunno what flavor of SQL you're in but you're probably looking at a Common Table Expression here. Something like this:

code:

WITH 1st_ekg ( ekg_pt, ekg_time )
AS
(
SELECT patient, lab_type, lab_time
FROM table
WHERE lab_type = 'ekg'
ORDER BY lab_time DESC
)

WITH 1st_mri ( mri_pt, mri_time )
AS
(
SELECT patient, lab_type, lab_time
FROM table
WHERE lab_type = 'mri'
ORDER BY lab_time DESC
)

SELECT table.patient, table.admission_time, 1st_ekg.ekg_time AS 1st_ekg_time, 1st_mri.mri_time AS 1st_mri_time
FROM table
INNER JOIN 1st_mri
ON table.patient = 1st_mri.mri_pt
INNER JOIN 1st_ekg
ON table.patient = 1st_ekg.ekg_pt

anthonypants fucked around with this message at 05:06 on Jun 19, 2018

# ? Jun 19, 2018 04:59

Hughmoris: Apr 21, 2007; Let's go to the abyss!

anthonypants posted:

Dunno what flavor of SQL you're in but you're probably looking at a Common Table Expression here. Something like this:

code:

WITH 1st_ekg ( ekg_pt, ekg_time )
AS
(
SELECT patient, lab_type, lab_time
FROM table
WHERE lab_type = 'ekg'
ORDER BY lab_time DESC
)

WITH 1st_mri ( mri_pt, mri_time )
AS
(
SELECT patient, lab_type, lab_time
FROM table
WHERE lab_type = 'mri'
ORDER BY lab_time DESC
)

SELECT table.patient, table.admission_time, 1st_ekg.ekg_time AS 1st_ekg_time, 1st_mri.mri_time AS 1st_mri_time
FROM table
INNER JOIN 1st_mri
ON table.patient = 1st_mri.mri_pt
INNER JOIN 1st_ekg
ON table.patient = 1st_ekg.ekg_pt

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

Moonwolf: Jun 29, 2004; Flee from th' terrifyin' evil of "NHS"!

anthonypants posted:

Dunno what flavor of SQL you're in but you're probably looking at a Common Table Expression here. Something like this:

code:

WITH 1st_ekg ( ekg_pt, ekg_time )
AS
(
SELECT patient, lab_type, lab_time
FROM table
WHERE lab_type = 'ekg'
ORDER BY lab_time DESC
)

WITH 1st_mri ( mri_pt, mri_time )
AS
(
SELECT patient, lab_type, lab_time
FROM table
WHERE lab_type = 'mri'
ORDER BY lab_time DESC
)

SELECT table.patient, table.admission_time, 1st_ekg.ekg_time AS 1st_ekg_time, 1st_mri.mri_time AS 1st_mri_time
FROM table
INNER JOIN 1st_mri
ON table.patient = 1st_mri.mri_pt
INNER JOIN 1st_ekg
ON table.patient = 1st_ekg.ekg_pt

This won't quite do it, it still produces all the rows. You'd want something like:

code:

WITH 1st_ekg ( ekg_pt, ekg_time )
AS
(
SELECT patient AS ekg_pt, MIN(lab_time) AS ekg_time 
FROM table
WHERE lab_type = 'ekg'
GROUP BY patient
)

WITH 1st_mri ( mri_pt, mri_time )
AS
(
SELECT patient AS mri_pt, MIN(lab_time) AS mri_time 
FROM table
WHERE lab_type = 'mri'
GROUP BY patient
)

SELECT table.patient, table.admission_time, 1st_ekg.ekg_time AS 1st_ekg_time, 1st_mri.mri_time AS 1st_mri_time
FROM table
INNER JOIN 1st_mri
ON table.patient = 1st_mri.mri_pt
INNER JOIN 1st_ekg
ON table.patient = 1st_ekg.ekg_pt

Although I'm more used to Oracle's subquery factoring (WITH clause) formatting so I'm not certain all the naming stuff is correct for SQL Sever, looks right following that doc.

# ? Jun 19, 2018 08:00

TheFluff: Dec 13, 2006; FRIENDS, LISTEN TO ME
I AM A SEAGULL
OF WEALTH AND TASTE

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:

SELECT patients.name, patients.admission_time, first_ekg.lab_time, first_mri.lab_time
FROM patients
CROSS APPLY (
  SELECT top(1) lab_procedures.lab_time
  FROM lab_procedures
  WHERE
    lab_procedures.patient_id = patients.patient_id AND
    lab_procedures.lab_type = N'ekg'
  ORDER BY lab_time DESC
) AS first_ekg
CROSS APPLY (
  SELECT top(1) lab_procedures.lab_time
  FROM lab_procedures
  WHERE
    lab_procedures.patient_id = patients.patient_id AND
    lab_procedures.lab_type = N'mri'
  ORDER BY lab_time DESC
) AS first_mri

Here's a more detailed explanation and performance comparison. Note that CROSS APPLY is the equivalent of an inner join, so if there's no matching rows in the right hand table (query?), nothing will be returned from the left hand table either. If you want nulls instead like with a left join, use OUTER APPLY.

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

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

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:

select patient_id,
       min(case when lab_type = 'ekg' then lab_time end) 1st_ekg_time,
       min(case when lab_type = 'mri' then lab_time end) 1st_mri_time
  from lab_table
 where lab_type in ('ekg','mri')
group by patient_id

That said, if the same person is admitted multiple times, do they get a different patient_id? Or is the combined key for patient and admittance instance available in the lab table? If neither is true, then assuming you want to only show the first ekg and mri for that particular hospital visit, some sort of cross apply would be necessary (but even then I think you could just cross apply one subquery with a group by and two min(case when...) columns).

# ? Jun 19, 2018 14:35

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

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:

select PI.PlayerName, count(KI.KillerName) as Kills , count(KI2.VictimName) as Deaths from Quake2Devastation.PlayerInfo PI
LEFT JOIN KillInfo KI on PI.PlayerName = KI.KillerName
LEFT JOIN KillInfo KI2 on PI.PlayerName = KI2.VictimName
group by PI.PlayerName
order by PI.playername

but I'm ending up with a product of kills times deaths. I could do this easily using temp tables, but I'm trying to do this elegantly in one statement. Can anyone help?

# ? Jun 19, 2018 21:23

Moonwolf: Jun 29, 2004; Flee from th' terrifyin' evil of "NHS"!

This should do it, aggregate your two subtotals separately and then join them on afterwards.

code:

SELECT
  PlayerName,
  Kills,
  Deaths
FROM
  Quake2Devastation.PlayerInfo LEFT JOIN
  (
    SELECT
      KillerName,
      count(*) as Kills
    FROM
      KillInfo
    GROUP BY
      KillerName
  ) KillCounts ON  PlayerName = KillerName LEFT JOIN
  (
    SELECT
      VictimName,
      count(*) as Deaths
    FROM
      KillInfo
    GROUP BY
      VictimName
  ) DeathCounts ON PlayerName = VictimName

If you still want to order it you could put it on at the end, don't order the subqueries.

# ? Jun 19, 2018 22:58

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

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

Moonwolf: Jun 29, 2004; Flee from th' terrifyin' evil of "NHS"!

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:

SELECT
  NVL(KillerName, VictimName) PlayerName,
  NVL(Kills, 0) Kills,
  NVL(Deaths, 0) Deaths
FROM
  (
    SELECT
      KillerName,
      count(*) as Kills
    FROM
      KillInfo
    GROUP BY
      KillerName
  ) KillCounts ON  PlayerName = KillerName FULL OUTER JOIN
  (
    SELECT
      VictimName,
      count(*) as Deaths
    FROM
      KillInfo
    GROUP BY
      VictimName
  ) DeathCounts ON KillerName= VictimName

Which saves a join.

Moonwolf fucked around with this message at 00:03 on Jun 20, 2018

# ? Jun 19, 2018 23:52

Hughmoris: Apr 21, 2007; Let's go to the abyss!

quote:

Hospital Lab SQL stuff...

Thanks everyone for the tips, I was able to get the results I needed.

# ? Jun 20, 2018 01:57

Ruggan: Feb 20, 2007; WHAT THAT SMELL LIKE?!

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.
SQL code:
select patient_id,
       min(case when lab_type = 'ekg' then lab_time end) 1st_ekg_time,
       min(case when lab_type = 'mri' then lab_time end) 1st_mri_time
  from lab_table
 where lab_type in ('ekg','mri')
group by patient_id
That said, if the same person is admitted multiple times, do they get a different patient_id? Or is the combined key for patient and admittance instance available in the lab table? If neither is true, then assuming you want to only show the first ekg and mri for that particular hospital visit, some sort of cross apply would be necessary (but even then I think you could just cross apply one subquery with a group by and two min(case when...) columns).

This is the correct way to approach that patient data problem imo. Conditional aggregates.

# ? Jun 20, 2018 13:46

Adbot: ADBOT LOVES YOU

# ? Jun 4, 2024 18:25

Thermopyle: Jul 1, 2003; ...the stupid are cocksure while the intelligent are full of doubt. �Bertrand Russell

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

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

«‹›303 »