120 reputation
6
bio website
location
age 34
visits member for 1 year, 9 months
seen Jul 21 at 19:30

Jul
16
awarded  Popular Question
Mar
8
comment Complex query with multiple normalized fields
Thank you, the query seems to work perfectly.
Mar
8
comment Complex query with multiple normalized fields
No worries. Thanks for answering anyway :)
Mar
8
comment Complex query with multiple normalized fields
@HLGEM This is an in-place design that I need to make more efficient as is. Thank you for the recommendation however
Mar
8
awarded  Commentator
Mar
8
awarded  Supporter
Mar
8
accepted Complex query with multiple normalized fields
Mar
8
comment Complex query with multiple normalized fields
PS This is a stopgap measure, we're looking into completely redesigning our data structure in the (hopefully) not too distant future, but needing to do what we can to improve performance in the meantime
Mar
8
comment Complex query with multiple normalized fields
Ok, to be clear, every non-null value in Data2 in the Raw Data is guaranteed to have a translation in the Translations table. However it is possible for Data2 to have null values on some or even most rows. So looks like Left Join and X, no "Coalesce", is the way to go. Thank you very much for your input, it's time for me to go off and build a test case to see if this helps our performance.
Mar
8
comment Complex query with multiple normalized fields
If, say, Data2 is null for a particular row and I'm selecting X as you mentioned, is the query going to be smart enough to insert null or do I need to add another condition?
Mar
8
comment Complex query with multiple normalized fields
By the time this data gets to the point of running this query, there will be a substitute value. We automatically generate those as needed as well, and that step happens first
Mar
8
comment Complex query with multiple normalized fields
For clarification, why would I use LEFT JOIN as opposed to INNER JOIN? Also, only PN and MFG are non-null in the data, does that affect your answer?
Mar
8
comment Complex query with multiple normalized fields
Since views are nothing more than stored select statements, I don't really see how this could help since my fundamental problem is writing a select query that accomplishes my goals
Mar
8
comment Complex query with multiple normalized fields
Creating separate lookup tables for each type of field may be possible, though I've already got something like 116 tables for this application, so adding a ton more might be a little painful. I'm not familiar with StoredProcedures - how would that work? And how much maintenance would be involved when you factor in about 40 different tables along the lines of what I described in my original question?
Mar
8
comment Complex query with multiple normalized fields
No, unfortunately a view won't help here for the sole reason that it is indeed that we're writing the translated table. The data from the translated field is never simply displayed, but queried against, and the enumerations are intended to speed up said queries on some really monstrous tables. The values are then reverse-translated on the fly for user display
Mar
8
revised Complex query with multiple normalized fields
added 21 characters in body
Mar
8
asked Complex query with multiple normalized fields
Mar
1
comment Better query to select multiple columns into a single output column?
Thank you for your reply. Doing some research on your suggestion leads me to believe that for the time being my existing solution is the most efficient option currently available for MySQL. I appreciate you taking time to answer my question.
Mar
1
awarded  Scholar
Mar
1
accepted Better query to select multiple columns into a single output column?