question

AlphaKilo avatar image
AlphaKilo asked

I need to choose a value from 1 table based on the values of another table that is joined.

This is the first time I'm posting a question. I am fairly new to SQL and have been struggling with this. The data is coming from 4 different tables. There can be multiple rows for each inmate depending on the charges they are arrested on. Any help with this will be greatly appreciated. Thanks. This is part of the data: booking# bookingdtime Last First ReleaseDate crimecode CrimeClass 1001 1/1/2012 5:34 G O 2/9/2012 7:37 4 Felony 1001 1/1/2012 5:34 G O 2/9/2012 7:37 15 Felony 1001 1/1/2012 5:34 G O 2/9/2012 7:37 18A Misdemeanor 1001 1/1/2012 5:34 G O 2/9/2012 7:37 18E Felony 1003 1/2/2012 0:28 G W 1/6/2012 7:43 6 Misdemeanor 1003 1/2/2012 0:28 G W 1/6/2012 7:43 21 Infraction 1004 1/2/2012 23:06 C J 1/5/2012 8:03 18A Felony 1004 1/2/2012 23:06 C J 1/5/2012 8:03 18E Felony 1004 1/2/2012 23:06 C J 1/5/2012 8:03 8 Misdemeanor 1005 1/3/2012 0:06 H P 1/12/2012 8:11 5 Felony 1005 1/3/2012 0:06 H P 1/12/2012 8:11 6 Felony 1006 1/3/2012 5:04 M J 1/5/2012 7:42 8 Misdemeanor 1006 1/3/2012 5:04 M J 1/5/2012 7:42 26 Misdemeanor 1007 1/3/2012 21:30 V K 1/9/2012 7:57 18A Felony 1008 1/3/2012 21:30 V K 1/9/2012 7:57 8 Misdemeanor 1008 1/3/2012 21:30 V K 1/9/2012 7:57 15 Misdemeanor 1009 1/3/2012 23:00 D Q 2/13/2012 7:34 5 Felony 1009 1/3/2012 23:00 D Q 2/13/2012 7:34 6 Felony 1010 1/5/2012 11:37 C D 6/8/2012 8:48 2 Felony I want the output to be. One row per inmate: booking# bookingdttime Last First ReleaseDate crimecode CrimeClass 1001 1/1/2012 5:34 G O 2/9/2012 7:37 4 Felony 1003 1/2/2012 0:28 G W 1/6/2012 7:43 6 Misdemeanor 1004 1/2/2012 23:06 C J 1/5/2012 8:03 18A Felony 1005 1/3/2012 0:06 H P 1/12/2012 8:11 5 Felony 1006 1/3/2012 5:04 M J 1/5/2012 7:42 8 Misdemeanor 1007 1/3/2012 21:30 V K 1/9/2012 7:57 18A Felony 1008 1/3/2012 21:30 V K 1/9/2012 7:57 8 Misdemeanor 1009 1/3/2012 23:00 D Q 2/13/2012 7:34 5 Felony 1010 1/5/2012 11:37 C D 6/8/2012 8:48 2 Felony
duplicates
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
I see 4 different crime codes and 3 different crime classes for Booking# 1001. What is the logic to decide on crimecode 4? Is it the lowest crimecode for the more serious crime class? Are there possible values for crimeclass outside of Felony and Misdemeanor?
1 Like 1 ·
AlphaKilo avatar image AlphaKilo commented ·
Thanks for your quick reply KenJ. There are 4 different crime classes: Felony, Misdemeanor, Infraction, DA Special Allegation with Felony being the highest, then Misdemeanor, Infraction, and DA. The value chosen needs to be based on that.
0 Likes 0 ·

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
I'd suggest you can do some variation of what I've done in my [article here on versioned data][1]. You'll have to have a mechanism of ranking the various crime classes within the database so that you can order the selection by them (if there's not a column for that, you'll need to physically add one). But once that's done, either a TOP 1 with an ORDER BY or the use or ROW_NUMBER for ordering will work. [1]: https://www.simple-talk.com/sql/database-administration/sql-strategies-for-versioned-data/
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.