Is this a JOIN situation?
Date: Mon, 14 Mar 2016 12:59:56 -0600
Message-ID: <2016031412595654666-_at_news.nmt.edu>
(Wasn't there a comp.lang.sql at one time?)
I've been writing Python programs that construct SQL commands to feed to MySQL/MariaDB for years, but now I'd like to see if I can do this with one command, instead of one, a little Python, and a bunch of queries.
table inventory
barcode
kingdom
phylum
class
make
model
style
table maint
barcode
date
entry
There's one entry per barcode in the inventory table, but as many as it takes entries per barcode in maint as it takes to document all of the times an item has been worked on.
select inz.barcode, inz.kingdom, inz.phylum, inz.class, inz.make, inz.model, inz.style, mdz.date, mdz.entry inventory as inz, maint as mdz where inz.barcode=mdz.barcode order by inz.kingdom, inz.phylum, inz.class, inz.make, inz.model, inz.style, mdz.date;
That works fine until I want the returned maint entries sorted by kingdom->style, but in reverse date order (desc). Just adding desc to the end gets kingdom->style also sorted in reverse order (like anti-alphabetical).
There's more to the selecting, like just the inz.phylum items that equal "SENSORS" and stuff like that that I don't have here, but this is the idea. The search can also be done where I just want maint entries made on or between certain dates, but I can make that all work fine when I just want the order of everything to be ascending.
I can do it in Python by getting the initial distinct list of inventory barcodes and then running a loop to get the maint entries for those barcodes in whichever order I want (this is all for eventually printing out the maint entries), but can it be done in just SQL? I've messed with the GROUP BY clause, but that didn't seem to work. I think I've hammered out two JOINs in my life, but I'm not even sure I understand
what I did there. :)Thanks!
Bob Received on Mon Mar 14 2016 - 19:59:56 CET