Is this a JOIN situation?

From: <Robert>
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

Original text of this message